I am currently attempting to replace an old Essbase cube with a Qlik Sense app. The cube pulls it's data from a semi-additive daily snapshot table in our data warehouse. The metric fields in that table are semi-additive in the sense that they can be summed across all dimensions except time.
To match the functionality of the cube, I have created several drill down dimensions, including one called Period (Year>Quarter>Month>Date). I would like to create a set analysis that sums the metrics by all dimensions, except the Period dimension. In the case of the Period dimension, I would like to app to display the last value (most recent) in the selected period.
When I am on the year level of the Period dimension, the app should show the metric value for the date of:
2015: 12/22/2015 (that is the most recent snapshot as of today)
When I am on the quarter level of the Period dimension, the app should show the metric value for the date of
Q4-15: 12/22/2015 (that is the most recent snapshot as of today)
When I am on the month level of the Period dimension, the app should show the metric value for the date of:
Dec-15: 12/22/2015 (that is the most recent snapshot as of today)
I have been able to connect the app directly to the cube, but I would like to eventually get rid of the cube. So, if anyone has any suggestions on how to accomplish using set analysis, I would greatly appreciate it.
Try sum(aggr(FirstSortedValue(Amount, -Date), Dim1, Dim2,..., DimN). Replace Amount and Date with the appropriate field names and replace Dim1, Dim2,..., DimN with the field names of the chart dimensions.
Thanks for you quick responses. Both helped me get to a solution. I forgot to mention in my first post that I have more than one type of measure in my fact table, so I need to use Set Analysis in addition to the functions. Here are two of the expressions that I am using in my app: