Cumulative total using Lists
I was looking at creating a measure for the Cumulative Quantities sold over time for individual products. I used the contextual "quick" cumulative measure within the discovery and it seemed to work as expected!
When adding a Year and Week filter to this discovery, the data was still producing the quantities by product that I would expect.
The next step was to dynamically filter the discovery to show the "Top 10 - Products over time". This is where the contextual cumulative measure fell over - I couldn't set the Top 10 filter to apply to this measure, it would apply to the original measure of "Quantity sold". Therefore showing the Top 10 products sold in that particular week, but the cumulative values for all time up to that week! (confusing).
I want to create the measure myself, so I went into formulate and was going to use a "Range List" to set the range from:
DATE = 01/01/1900 to DATE = "Current Member"
When trying to use Current Member I am getting the following error message: "The RANGE function expects a NAMED MEMBER expression for the 2 argument. A CONTEXTUAL SET expression was used."
Can someone explain to me if this is expected, because if it is then why is the option to use the "CONTEXTUAL SET" available on a Range List. However, if it isn't expected, it would be great to get it to work so that we can use dynamic "to date" measures without using time intelligence functions
2 replies
-
Elliot Webb ,
“Context” measures can’t be used for filtering/sorting, as they are a “post result” calculations.
You can filter/sort by the base measure, but not by a “visual” calculation…For example, think of the grid in the below image.
If you say “filter countries where cumulative sales > 20M”
So you want to remove Australia, right?
But then the cumulative sales of Canada isn’t 24M anymore, but actually 6M.Also in sorting for example…
You can’t order by cumulative. It’s always ascending.
If you change the order of the rows (sorting by label descending for example) you’ll still get the values in ascending order. Because first the order is processed and only at “render” time, the cumulative is calculated. So you also can’t sort by it.In cumulative the “top 10” are just the “last 10”.
So you could ask for “last 10” in the filter, without a measure…
See image below, (example with Top 3)
Regarding enabling “Context” measures to be edit/shareable,
It will be available in the future. We currently don’t allow you to create these in PQL so it’s still blocked, hence why you can't create the measure yourself.Kind Regards,
Yakov. -
When will contextual sets be enabled? its preventing me doing loss ratios as I use a contextual measure for premium and loss but unable to do one for loss ratio as its on a line by line basis. If i can create the formula myself then I can use the the formulas created in loss ratio calculation.