Filtering with Dates in Set Analysis - Not working

[Anonymous]

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.

[sushil353]

Try this:

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

HTH

[Anonymous]

sushil353‌ It is also not working mate..

[MK_QSL · Accepted solution]

Use variable as below..

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

Now use below expression

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

[Anonymous]

mrkachhiaimp‌ Thanks Manish, it is working perfectly.

[Anonymous]

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

[MK_QSL]

Because you have used SET variable inside script..

The Magic of Variables