topic Re: YTD calculation that works with filtered date dimension in QlikView

YTD calculation that works with filtered date dimension

MynhardtBurger — Tue, 20 Mar 2018 11:37:03 GMT

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

Re: YTD calculation that works with filtered date dimension

sunny_talwar — Tue, 20 Mar 2018 17:09:32 GMT

Try this

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

Re: YTD calculation that works with filtered date dimension

MynhardtBurger — Wed, 21 Mar 2018 15:54:43 GMT

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:

Re: YTD calculation that works with filtered date dimension

sunny_talwar — Wed, 21 Mar 2018 16:11:14 GMT

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

Re: YTD calculation that works with filtered date dimension

sunny_talwar — Wed, 21 Mar 2018 16:13:01 GMT

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)))

Re: YTD calculation that works with filtered date dimension

Anonymous — Thu, 22 Mar 2018 03:11:21 GMT

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

Re: YTD calculation that works with filtered date dimension

MynhardtBurger — Thu, 22 Mar 2018 14:31:47 GMT

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.