topic Re: Max Date, Variables & Set Analysis in QlikView

Max Date, Variables & Set Analysis

Anonymous — Thu, 18 May 2017 12:35:27 GMT

Here is what I am trying to accomplish: Use a variable to get the max(Date) and return that amount only in KPI Object basically to get the sum of the max(date) and ignore the other values

I have data like this

Date: Sum( Dollars)

3/31/2017 1500.00

2/28/2017 100.00

1/31/2017 400.00

I have it working for hardcoding the value but created a variable called vMaxDate and I have tried variations on this but to no success.

Sum({1<AS_OF_DT = {"03/31/2017"}>} SALES)

Any help would be greatly appreciated!

Re: Max Date, Variables & Set Analysis

Anonymous — Thu, 18 May 2017 13:56:02 GMT

Various versions I've tried and just seems to either have syntax incorrect or evaluating the vMaxDate as number not date

Sum({1<AS_OF_DT={$(vMaxDate)}>} SALES)

vMaxDate = Max(AS_OF_DT)

Re: Max Date, Variables & Set Analysis

Anil_Babu_Samineni — Thu, 18 May 2017 16:05:43 GMT

How is your environment looks like for DateFormat?

Can you change it to

SET DateFormat='MM/DD/YYYY';

from

SET DateFormat='DD-MM-YYYY';

Then Try ??

Re: Max Date, Variables & Set Analysis

Anonymous — Thu, 18 May 2017 16:13:03 GMT

try

vMaxDate=date(max(AS_OF_DT),'M/D/YYYY')

and

Sum({1<AS_OF_DT={'$(vMaxDate)'}>} SALES)

Re: Max Date, Variables & Set Analysis

Anonymous — Thu, 18 May 2017 18:24:10 GMT

I set them like this:

SET DateFormat='MM/DD/YYYY';

SET TimestampFormat='MM/DD/YYYY h:mm:ss[.fff] TT';

Originally they were 'M/D/YYY';

Re: Max Date, Variables & Set Analysis

Anonymous — Thu, 18 May 2017 18:28:21 GMT

I tried that and several combinations on that. I do have a master calendar that I just added so I may try going that route.

This works that I've used but can't get the selection part to work but it does for other KPI's that I've created

Works: sum(if(AS_OF_DT=vMaxDate,Sales))/1000000000 but the selecting dimensions changes the values and for this one I would not want it to

Another KPI on that sheet works :Sum({1<AS_OF_DT_YYYY={$(vCurrentYear)}>} SALES)/1000000000

Will continue to work on this but thanks for responses.

Re: Max Date, Variables & Set Analysis

Anonymous — Thu, 18 May 2017 18:42:16 GMT

Do you mean ignore the selection in the calculation of your max date?

That would be in your variable declaration. Something like:

vMaxDate=date(max({1}AS_OF_DT),'M/D/YYYY')

Re: Max Date, Variables & Set Analysis

Anonymous — Thu, 18 May 2017 19:25:42 GMT

This data is by month end only trending back 3 years so I have a variable set to pull max(AS_OF_DT) in this case 3/31/2017. I also have variable based off that variable that would pull out just the year(vMaxDate)-1 to pull all of 2016 etc for 2015...sample made up data but principle is here and I have most of this working.

If you think of it this way one set KPI's to never change at the company level, and another set KPIs to change with the selection the user has, in this case they would most likely select the BUSAREA they are interested in but would still see the company level KPIs above them.

Maybe I'm overcomplicating this.

KPI	AS_OF_DT	Values	Behavior Desired
Most Recent Month Sales	03/31/2017	$509,918	No change when selections made
PREV YEAR SALES	All 2016	$323,767	Change with selection
2 YEAR PREV SALES	All 2015	$483,835	Change with selection

AS_OF_DT	BUSAREA	SALES	Tax
03/31/2017	A	$37,313	$933
03/31/2017	B	$52,643	$1,316
03/31/2017	C	$1,193	$30
03/31/2017	D	$81,166	$2,029
02/28/2017	A	$36,813	$920
02/28/2017	B	$52,663	$1,317
02/28/2017	C	$1,180	$29
02/28/2017	D	$80,497	$2,012
01/31/2017	A	$35,773	$894
01/31/2017	B	$51,476	$1,287
01/31/2017	C	$1,160	$29
01/31/2017	D	$78,041	$1,951
12/31/2016	A	$34,568	$864
12/31/2016	B	$50,380	$1,260
12/31/2016	C	$1,123	$28
12/31/2016	D	$75,998	$1,900
11/30/2016	A	$34,498	$862
11/30/2016	B	$50,330	$1,258
11/30/2016	C	$1,158	$29
11/30/2016	D	$75,711	$1,893
10/31/2015	A	$34,302	$858
10/31/2015	B	$49,800	$1,245
10/31/2015	C	$1,121	$28
10/31/2015	D	$74,448	$1,861
09/30/2015	A	$34,722	$868
09/30/2015	B	$50,645	$1,266
09/30/2015	C	$1,121	$28
09/30/2015	D	$76,047	$1,901
08/31/2015	A	$34,402	$860
08/31/2015	B	$50,522	$1,263
08/31/2015	C	$1,128	$28
08/31/2015	D	$75,577	$1,889

Re: Max Date, Variables & Set Analysis

Anonymous — Thu, 18 May 2017 19:39:35 GMT

This should have worked

Sum({1<AS_OF_DT={'$(vMaxDate)'}>} SALES)

But you could also try

Sum({<AS_OF_DT={'$(vMaxDate)'}>} TOTAL SALES)

Sum({<AS_OF_DT={'$(vMaxDate)'},BUSAREA=>} SALES)

Re: Max Date, Variables & Set Analysis

Anonymous — Wed, 14 Jun 2017 14:47:38 GMT

Thanks Wallo it was an issue I was having with variable and works much appreciated!