Show values in text box with changing year information

[ZoeM]

Hi Qlik Community.

I have a text box overlaying a pivot chart, and in this text box I want to show the difference between the years selected of the stated expression.

My expression counts the number of late products year over year, when the user selects two subsequent years i want the text box to show the difference between those two years. My current expression is:

=count({$<[#of Days Post VPP]={">14"},CY=Max({<[CY]=>}[CY])>}CY)

I am sure there is a simple fix to this and I am just glossy eyed.

Thanks in advance.

[sunny_talwar]

May be this

=Count({$<[#of Days Post VPP]={">14"}, CY = {"$(=Max([CY]))"}>} CY)

[vishsaggi]

Try this?

= count({$< [#of Days Post VPP]={">14"},

                   CY = {"$(=Max({<[CY]=>}[CY]))"} >} CY)

[ZoeM]

When I modify the example like the below, I do not get the correct value (9), difference between 2017 and 2018:

= (count({$< [#of Days Post VPP]={">14"},CY = {"$(=Min({<[CY]=>}[CY]))"} >} CY))-(count({$< [#of Days Post VPP]={">14"},CY = {"$(=Max({<[CY]=>}[CY]))"} >} CY))

Values:

2016

# of late gateways = 54

2017

# of late gateways = 45

2018

# of late gateways = 5

I cannot use absolute references because the pivot chart changes across years and can compare 205 to 2017 etc...

[sunny_talwar]

I am confused... you are saying that

(count({$< [#of Days Post VPP]={">14"},CY = {"$(=Min({<[CY]=>}[CY]))"} >} CY)) = 54

(count({$< [#of Days Post VPP]={">14"},CY = {"$(=Max({<[CY]=>}[CY]))"} >} CY)) = 5

and you want there subtraction to be 9? 54 - 5 = 49? Are you at least getting 49?

[ZoeM]

The expression as you have written it should equal 49 (2016 MinYr - 2018 MaxYr), but it is not.

and I am unable to get

[sunny_talwar]

Can you show me the individual pieces

1st -> (count({$< [#of Days Post VPP]={">14"},CY = {"$(=Min({<[CY]=>}[CY]))"} >} CY)) in a text box is 54

2nd -> (count({$< [#of Days Post VPP]={">14"},CY = {"$(=Max({<[CY]=>}[CY]))"} >} CY)) in a text box is 5

[ZoeM]

I have both scenarios below. The min in the below example is 2016 and the value obtained from the expression should be 54. The max is 2018 and the value obtained should be 5. But from the snippets, you can see what values I am getting.

[sunny_talwar]

So, your expressions are not right. What exactly are you trying get? Can you share a sample as I have no idea what you have. I thought you already have your expressions figured out expect the set analysis part... but it appears the whole thing is not right

[ZoeM]

So my final intent is to show the difference between the years that the user selects. If the user selects to compare 2016 and 2017, the text box should show the difference between the two selected years. The pivot table in the back changes according to the selections. If only one year is selected, only year will show, two years selected then two years show etc.

My current expression works when one year is selected:

But when I select two years, say 2017 to compare against 2016 for instance, the expression I have will sum the # Late Gateways  and will have a result of 99 (54 from 2016 + 45 from 2017):

So I need to add to the current set analysis expression:

1. Another expression that emphasizes the min year

2. Subtract the min year # of Late Gateways from max year # of Late Gateways.

The end result should be:

2017 # of Late Gateways (45) subtract 2016 # of Late Gateways (54) = -9 reduction in late gateways YoY.

Help