You have two options for this - go the OAC semantic layer (not just a manually created datasource), and create your datasource in the semantic layer (RPD). There you can manage cache refreshes with some set frequency. This requires using the Admintool to design RPD or - keep the source you have now, make it Live, not…

Hi Brian, - if your dataset is set with the data access rule of 'Automatic Caching' (default value, you can see by editing your dataset), it will only look at the dataset cached in OAC. In this case you need to run refresh datasets to refresh everying - if your dataset is set with the 'Live' option, then a simple refresh…

Hello Brian, can you confirm you have the Physical SQL from the DV query, and not just the report Logical SQL ? Another way to check that :can you confirm that your DV data-source has its data-access set to Live and not Automatic Caching in it's property. You can see that and change it by clicking Inspect on your dataset.…

Hi Loret, Christian there is actually a way to share Data-Flows today, not ultra-intuitive but robust. When you build a dataflow that you want to export and share, simply do this : - run the Data-Flow and create a dataset with it (even if 'dummy' data only) - create a project on this resulting dataset (even a 'dummy'…

Hello Gary and Christian From what I know as of now, RPD remains the mainstream solution to connect DV with PBCS. May I ask more details as to what is driving the need for an alternative to RPD in this case ? Let me know, this will help thanks Philippe

Hello Voon, thanks for having confirmed the testing. I believe the msum gives a precedence to sorting on date. Hence, as the sorting is on date first, the msum values do not sequence properly by item in your example. So, - the simplest solution, if acceptable, would be to have two vizs : the one where the MSUM is would not…

Hi Voon, in the viz that you send in your screenshot, can you test simply removing the date column and confirm if the calculation is still not working ? let me know pls Philippe

Thanks Robert, I was able to download the file. Indeed your data is not dense on time (some months are missing when at this level of filtering, see barchart below. OAC can handle this but only by defining a time hierarchy in its metadata layer. The direct connection to the db that you currently have with DV is not yet able…

Hi Craig, assuming your data is not from an OAC RPD with a time hierarchy defined in it. That is, data is from direct simple flat source (file, db). You can achieve this calculation using an MSUM formula. Check this brief video about it : Oracle DV - How to Calculate Period Ago Without A Time Hierarchy. however, if you are…

notice that in the example above, the sorting of the data is critical to the calculation. Also, your question about difference between two dates (any dates, not necesseraly two subsequent rows) requires a different syntax. Can you give an example of what you are trying to do ? Philippe

Voon, sorry my message may not have been clear, the syntax I gave was symbolic (not to be text-copied in DV). Here is a screenshot of a calculation that computes the difference between row and previous row in a report. Let me know if not what you needed. Thanks Philippe

Hi Robert, I was able to get your project, but it's source is a database. unfortunately I wont be able to connect to your database. To share this project you may need to create a file based version of your example unfortunately. Possible ?

Hello Voon, if your need is to calculate the difference of metric M between row n and row n-1, you can use msum function this way : the value of M.row n-1 is equal to : msum(M, 2) - M.row n hence calculating the difference M.row n - M.row n-1 comes down to entering this formula in your cell : M.row n - (msum(M, 2) - M.row…

The conditional statement has to exist in the definition of the answer report filter. Attached is a short gif video showing you one place where you may configure it. Philippe

Robert, any chances you can share a DVA with a subset of your data as a source to it, so I could take a look ? Philippe

Hello Robert, this 3 min video https://youtu.be/LkRHUlXvTbQ shows you a way to configure the MSUM function so that you can use it with a dimension breakdown. This is with using the BY clause in the syntax of the function. Let me know if this is not helping with your initial issue. Tks, Philippe

My suspicion is that body-part in rows is what is creating the MSUM not to work. MSUM wihtout body-part in rows should toally work, not sure you confirmed that in your note above. can you re confirm it works in this case ? Thanks

There probably exist other ways to do this (not sure) but one that comes to my mind is using a prompt to set a presentation variable (vanilla option in the prompt definition) : your prompt has two text choices for the user : include or exclude. These choices get passed on by the prompt to a pres variable 'PX' that you…

Also, if you are planning to use Count(*) with breakdowns analysis, I recommend you add an extra column in your dataset (prepare tab) with a dummy 1 value column, and sum it. Just prudent practice. Philippe

Robert, it's likely that a sorting may be the issue here. Can you please try this and confirm : run the same report, but without body-part in rows. Confirm the MSUM november value is correct in this case. Then, filter for a single body part, and confirm that the msum is working fine as well. This is not yet the answer to…