topic Re: Date and Set Analysis in QlikView

Date and Set Analysis

jdmarlin — Thu, 03 Sep 2015 20:45:46 GMT

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)

Re: Date and Set Analysis

cspencer3 — Thu, 03 Sep 2015 20:49:27 GMT

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

Re: Date and Set Analysis

jdmarlin — Thu, 03 Sep 2015 20:53:14 GMT

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

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

Re: Date and Set Analysis

cspencer3 — Thu, 03 Sep 2015 20:56:59 GMT

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

Re: Date and Set Analysis

jdmarlin — Thu, 03 Sep 2015 21:01:16 GMT

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!

Re: Date and Set Analysis

cspencer3 — Fri, 04 Sep 2015 12:24:11 GMT

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!

Re: Date and Set Analysis

kavita25 — Fri, 04 Sep 2015 13:05:10 GMT

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

Re: Date and Set Analysis

MarcoWedel — Fri, 04 Sep 2015 23:55:05 GMT

Hi,

maybe also possible:

hope this helps

regards

Marco