topic Re: Show values in text box with changing year information in QlikView

Show values in text box with changing year information

ZoeM — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Show values in text box with changing year information

sunny_talwar — Tue, 24 Apr 2018 19:54:30 GMT

May be this

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

Re: Show values in text box with changing year information

vishsaggi — Tue, 24 Apr 2018 19:56:28 GMT

Try this?

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

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

Re: Show values in text box with changing year information

ZoeM — Tue, 24 Apr 2018 20:08:49 GMT

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

Re: Show values in text box with changing year information

sunny_talwar — Tue, 24 Apr 2018 20:50:43 GMT

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?

Re: Show values in text box with changing year information

ZoeM — Tue, 24 Apr 2018 20:57:30 GMT

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

and I am unable to get

Re: Show values in text box with changing year information

sunny_talwar — Tue, 24 Apr 2018 20:59:24 GMT

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

Re: Show values in text box with changing year information

ZoeM — Tue, 24 Apr 2018 21:08:43 GMT

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.

Re: Show values in text box with changing year information

sunny_talwar — Tue, 24 Apr 2018 21:11:33 GMT

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

Re: Show values in text box with changing year information

ZoeM — Tue, 24 Apr 2018 21:22:18 GMT

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

Re: Show values in text box with changing year information

sunny_talwar — Tue, 24 Apr 2018 21:29:47 GMT

CY is a year field, right? How do you create that in the script?

Re: Show values in text box with changing year information

ZoeM — Tue, 24 Apr 2018 21:35:38 GMT

Yes CY is the year field.

The data is from a Power Query attached to a library data source and comes into QV that way. In the Script I renamed it so as to create a link to another table: