Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
egemenekinci
New Contributor III

Date Variable in Set Analysis

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

1 Solution

Accepted Solutions

Re: Date Variable in Set Analysis

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

6 Replies

Re: Date Variable in Set Analysis

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

egemenekinci
New Contributor III

Re: Date Variable in Set Analysis

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.

Highlighted

Re: Date Variable in Set Analysis

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?

MVP
MVP

Re: Date Variable in Set Analysis

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.

MVP & Luminary
MVP & Luminary

Re: Date Variable in Set Analysis

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

egemenekinci
New Contributor III

Re: Date Variable in Set Analysis

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.