Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have the following sample data:
Year | Month | YearMonth | Amount |
---|---|---|---|
2018 | 1 | 201801 | 1 |
2018 | 2 | 201802 | 2 |
2018 | 3 | 201803 | 3 |
2018 | 4 | 201804 | 4 |
2018 | 5 | 201805 | 5 |
2018 | 6 | 201806 | 6 |
2018 | 7 | 201807 | 7 |
2018 | 8 | 201808 | 8 |
2018 | 9 | 201809 | 9 |
2018 | 10 | 201810 | 10 |
2018 | 11 | 201811 | 11 |
2018 | 12 | 201812 | 12 |
I can calculate the YTD on a straight table like so:
However when ever I make a selection on the Month field, the YTD calculation breaks like so (month 6 and 10 are selected):
The required result is:
Month | YTD Total |
---|---|
6 | 21 |
10 | 55 |
Is there a way to write the expression to calculate a YTD total, even when the date dimension in the visualisation is filtered?
I'd like to avoid calculating YTD totals during the script load stage.
Thank you
Since you are using Qlik Sense, you can def. create a sort independent expression using Aggr() function
Aggr(RangeSum(Above(Sum({<[Month]>}Amount), 0, [Month])), (Month, (NUMERIC)))
Read more about this here
Try this
RangeSum(Above(Sum({<Month>}Amount), 0, Only({1}Month))) * Avg(1)
Thank you Sunny. Your suggested expression does work.
Are the following bold elements of the formula necessary?
RangeSum(Above(Sum({<Month>}Amount), 0, Only({1}Month))) * Avg(1)
The following formula appears to work the same: RangeSum(Above(Sum({<[Month]>}Amount), 0, [Month]))
Is it possible to write such a YTD expression which isn't dependent on the sort order?
The suggested formula breaks as soon as the [Month] field's sorted in descending order from 12 to 1 (or any other sort order which isn't strictly ascending). See the example below:
1) Avg(1) will only return for Month which are selected, for anything not selected... Avg(1) = 0. This is what I am using to remove Months which are not selected, because I am ignoring selection in the Month field in the main expression
2) Why will it return Null? Month is a dimension and for every month... you have a possible month (sounds stupid... but it is true, right?)
3) I think it should not matter, but I just add it to be safe... feel free to remove it... if it works without Only and {1} after selection also
Since you are using Qlik Sense, you can def. create a sort independent expression using Aggr() function
Aggr(RangeSum(Above(Sum({<[Month]>}Amount), 0, [Month])), (Month, (NUMERIC)))
Read more about this here
Use All member filter, so that irrespective of your date selection you would results,
{Starting selection <Dimension1 = {*} >} ({*} for numeric, {"*"} for text)
Or:
{Starting selection <Dimension1 = >}
Thank you!
In my previous attempts I got quite close to the solution, but ended up coming up just short due. Thanks for highlighting some of the areas where I misunderstood how the formulas worked.