topic Re: Set Analysis With a Date Variable in QlikView

Set Analysis With a Date Variable

Thu, 04 Aug 2016 16:18:47 GMT

Hey everyone, I am having some issues with my set analysis. This is what I currently have.

Variable:

vMaxTransDate = max(TranDate)

Set Analysis:

=sum({<SalesStat = {'Open'}, TranDate = {'=$(vMaxTransDate)'}>}SalesQty)

This is not giving me the SalesQty for that max date but instead its giving me all the SalesQty till that max date. Would love some help with this. Thank you in advance.

Re: Set Analysis With a Date Variable

sunny_talwar — Thu, 04 Aug 2016 16:31:41 GMT

Try one of these:

=Sum({<SalesStat = {'Open'}, TranDate = {'$(=$(vMaxTransDate))'}>}SalesQty)

=Sum({<SalesStat = {'Open'}, TranDate = {'$(=vMaxTransDate)'}>}SalesQty)

Re: Set Analysis With a Date Variable

swuehl — Thu, 04 Aug 2016 16:34:22 GMT

Using an equal sign in the field modifier starts an advanced search, which you don't want here, you want to use a constant value element list.

Take care to format your value according to your format of TranDate:

Variable:

vMaxTransDate

=Date(max(TranDate),'MM/DD/YYYY')

// Format according to your TranDate field format and use a leading equal sign in definition

Set Analysis:

=sum({<SalesStat = {'Open'}, TranDate = {'$(vMaxTransDate)'}>} SalesQty)

Dates in Set Analysis

The Magic of Variables

Re: Set Analysis With a Date Variable

Thu, 04 Aug 2016 17:06:56 GMT

=sum({<SalesStat = {'Open'}, TranDate ={'$(=$(vMaxTransDate))'}>}SalesQty)

That worked. Thank you. Can you explain why this worked?

Re: Set Analysis With a Date Variable

swuehl — Thu, 04 Aug 2016 18:59:54 GMT

That worked. Thank you. Can you explain why this worked?

I assume you haven't defined your variable with a leading equal sign, so you need the equal sign in the set modifier to evaluate your max() function, right?

Re: Set Analysis With a Date Variable

Thu, 04 Aug 2016 19:08:24 GMT

Yeah, you are correct. Thank you.

Re: Set Analysis With a Date Variable

wiz_nerd — Tue, 12 Jan 2021 10:17:57 GMT

Small changes that makes calculation work. 🙂