Date Variable in Set Analysis

[Anonymous]

Dear experts,

I am quite new in qlikview and having a problem with working variables with dates. My date fields seems correct but I can not get correctly work sum function with variable filter in set analysis.

I have attached a sample app and your help will be appreciated.

Thanks, !

[sunny_talwar · Accepted solution]

Try this:

Sum({<[Apprv. Dt.]={"<=$(=Date(vCalcDate, 'MM-DD-YYYY'))"}>}GWP)

Default format for your date field is MM-DD-YYYY, so you need to specify your date within set analysis in that same format. Read here about Dates in Set Analysis

[Anonymous]

Dear Sunny,

Thanks for this rapid response. I got your point but one more thing to clarified. I have already define date format in load script as below

LET vCalcDate =Date(Date#('08/31/2016','MM/DD/YYYY'),'MM/DD/YYYY');

Why do I need to define it again in set analysis ?

Thank you for taking the time.

[sunny_talwar]

The problem I foresee is that variable in script as stored as text, it might create issue, but until tested, I won't be 100% sure. Is this not working in your case?

[johnw]

To avoid the need to get the date format correct, I like using... I think I first saw it in a post by troyansky? Apologies if I'm improperly attributing it, and apologies if I get this syntax a little wrong. I'm eternally slightly messing up my set analysis and needing to fix it.

sum({<[Apprv. Dt.]={"=[Apprv. Dt.]<=$(vCalcDate)"}>} GWP)

I don't know if a variable defined as you have it will keep that format. Probably, but I'm not certain. But with the above, you don't have to worry about format, so it should work regardless. And you could also define it something like this, with no formatting at all:

LET vCalcDate = makedate(2016,8,31);

Nothing wrong with what you have now, though, at least that I can see.

[Oleg_Troyansky]

John nailed it right - using the Advanced Search is preferable compared to Simple Search because you don't have to worry about formatting your dates in a particular way.

The only addition I could offer is this - another advantage of the Advanced Search is that you can use your Variables without the $-sign expansion, and that is another possible problem that can be avoided. So, the best syntax in this case is this:

sum({<[Apprv. Dt.]={"=[Apprv. Dt.]<=vCalcDate"}>} GWP)

Cheers,

Oleg Troyansky

Learn Set Analysis and other advanced Qlik techniques in my book QlikView Your Business

[Anonymous]

Thanks all for your contributions. And special thanks to johnw‌ letting me know that advanced search and credits to troyansky‌. It seems quite useful and handy.