Date and Set Analysis

[jdmarlin]

Struggling to understand set analysis and date variables...

[Analysis Date]

09/01/2015

09/02/2015

Document Variable

vMaxDate = Max([Analysis Date])

In a text box =vMaxDate appears as...

42249

This set analysis doesn't work:

Sum({$<[Analysis Date]={$(=vMaxDate)}>} Value)

[cspencer3]

try

Sum({$<[Analysis Date]={$(=date(vMaxDate,'MM/DD/YYYY')}>} Value)

that might tell you if your date field is being stored as a number or not. I am not certain that will work though...

[jdmarlin]

I get an 'Allocated memory exceeded' issue with that.

When I do [Analysis Date]={'09/02/2015'}, it works fine though.

[cspencer3]

This just worked for me in a test app:

Sum({$<[Analysis Date]={"$(=date(vMaxDate,'MM/DD/YYYY')"}>} Value)

double quotes are added.

However i did not do this with a variable...

[jdmarlin]

Unfortunately still getting the same error on my end "allocated memory exceeded"

This oddly worked though:

Sum({$<[Analysis Date]={'=Aggr(If(Rank([Analysis Date])=1,[Analysis Date]),[Analysis Date])'}>} Value)

For me set analysis and dates = POISON!

Thanks for your help Charles!

[cspencer3]

I hear you there. Dates in Qlik can be very odd.

That has to be the most backwards solution to your problem, but at least it worked! It should be simpler than using AGGR and RANK to get the MAX date!

[kavita25 · Accepted solution]

As your date field is MM/DD/YYYY format..

You have make the same format while creating variables and used in Set analysis

Variable:

= date(Max( Date),'MM/DD/YYYY')

Set Analysis:

=Sum({$< Date={'$(=vMaxDate)'}>} Values)

As you are passing the variable in date field in set analysis, its necessary to make the variables in the same format as it is in the date field..

Hope it helps you!!!

[MarcoWedel]

Hi,

maybe also possible:

hope this helps

regards

Marco