Max Date, Variables & Set Analysis

[Anonymous]

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!

[Anonymous]

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)

[Anil_Babu_Samineni]

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

[Anonymous]

try

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

and

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

[Anonymous]

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';

[Anonymous]

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.

[Anonymous · Accepted solution]

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

[Anonymous]

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

[Anonymous]

This should have worked

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

But you could also try

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

or

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

[Anonymous]

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