YTD calculation that works with filtered date dimension

[MynhardtBurger]

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

[sunny_talwar]

Try this

RangeSum(Above(Sum({<Month>}Amount), 0, Only({1}Month))) * Avg(1)

[MynhardtBurger]

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)

• Avg(1) should always return 1, so what is the point of multiplying by 1?
• Only() will return the value or null (if there are multiple occurrences). Won't this possibly introduce bugs if NULL is returned?
• {1} doesn't appear to matter, whether I filter on the [Month] field or not.

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:

[sunny_talwar]

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

[sunny_talwar · Accepted solution]

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

The sortable Aggr function is finally here!

[Anonymous]

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 = >}

[MynhardtBurger]

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.