topic Re: Filtering with Dates in Set Analysis - Not working in QlikView

Filtering with Dates in Set Analysis - Not working

Anonymous — Tue, 21 Jun 2016 08:55:25 GMT

Dear experts,

I am trying to filter data with sum function in expression and somehow it is not working. I have searched the same topics and tried to implement solutions which were suggested before but no luck.

I have a variable a like that : SET vCalcDate=Date('04/30/2016','MM/DD/YYYY');

and want to bring the sums according to this variable .

Script sample,

PolData:

LOAD PolNo,

[Approval Data] as [APPROVAL DATE],

UWY,

Premium AS GWP,

[Start Date] AS [UPR START DATE],

[End Date] AS [UPR END DATE]

FROM

(ooxml, embedded labels, table is Sheet1);

I am using this very simple formula and having null results.

sum({<[APPROVAL DATE]={"<=$(vCalcDate)"}>} GWP)

I have attached a sample data and your helps will be appricated.

Re: Filtering with Dates in Set Analysis - Not working

sushil353 — Tue, 21 Jun 2016 09:03:30 GMT

Try this:

sum({<[APPROVAL DATE]={"<=$(=vCalcDate)"}>} GWP)

HTH

Re: Filtering with Dates in Set Analysis - Not working

Anonymous — Tue, 21 Jun 2016 09:13:15 GMT

sushil353‌ It is also not working mate..

Re: Filtering with Dates in Set Analysis - Not working

MK_QSL — Tue, 21 Jun 2016 09:43:40 GMT

Use variable as below..

SET vCalcDate = Date(Date#('04/30/2016','MM/DD/YYYY'));

Now use below expression

=SUM({<[APPROVAL DATE] = {"<=$(=$(vCalcDate))"}>}GWP)

Re: Filtering with Dates in Set Analysis - Not working

Anonymous — Tue, 21 Jun 2016 09:52:55 GMT

mrkachhiaimp‌ Thanks Manish, it is working perfectly.

Re: Filtering with Dates in Set Analysis - Not working

Anonymous — Tue, 21 Jun 2016 09:59:01 GMT

Also can you explain why we are using 2 dollar expansion in this formula ? Thanks.

Re: Filtering with Dates in Set Analysis - Not working

MK_QSL — Tue, 21 Jun 2016 12:18:03 GMT

Because you have used SET variable inside script..

The Magic of Variables