Set Expression Working in QlikView, But Not in Qlik Sense

[asmithids]

Hello,

I am trying to create a Balance Sheet.  I am using the following expression to aggregate (i.e. as of balance) the amounts per GL number.  The following set expression is working in QlikView, but it does not work in Qlik Sense.

Sum({$<Year={"<=$(vDate)"}>} SalesAmount)

I set the variable in Qlik Sense with the following:

SET vDate = [Year];

I know the variable is working in Qlik Sense because I created a straight table with the vDate variable as a dimension (see attached screen shot).

Has anyone seen this issue? 

Thank you in advance for any assistance. 

[mjm]

Hello Alec,

QlikSense and Qlik View are based on the same engine so the same script should work in both products. From lookin at your screenshot you have a field in there called FiscalYear. I would suggest then that the set analysis code won't work as Year doesn't exist but is actually FiscalYear. Have you tried the same code but with FiscalYear instead, e.g:

Sum({$<FiscalYear={"<=$(vDate)"}>} SalesAmount)

I believe this will help. Please mark as helpful if this helps.

Thanks,

[asmithids]

Hi Mark,

Thank you for the response.  Fiscal Year is the label of the actual field called Year.  When I changed the expression to the below, the straight table does work.  However, the set expression below is only aggregating the year selected in the filter.  It is supposed to aggregate all years prior to the selected year (i.e. Filter on 2014, the years to aggregate should be 2010-2014).

Sum({$<FiscalYear={"<=$(vDate)"}>} SalesAmount)

Very strange...  My original expression works fine in QlikView. 

[mjm]

Hello,

Does this community post help you further?

This explains how you can take the current year for example and the year previous

Set Analysis for Current week &amp; previous week

[asmithids]

Hi Mark,

I think it will.  Thank you again!

[mjm]

Great Thanks Alex. Please can you mark this response as helpful if you consider this to be resolved?

Thanks,

[mjm]

Sorry Alex, but I actually also meant to ask you to mark this as resolved if you consider it to be resolved.

Thanks

[rubenmarin]

Hi Alec, for the expression:

Sum({$<Year={"<=$(vDate)"}>} SalesAmount)

and the variable:

SET vDate = [Year];

It will return

Sum({$<Year={"<="}>} SalesAmount) //If no Year is selected (there is more than one value, so returns Null())

Sum({$<Year={"<=2015"}>} SalesAmount) // If Year 2015 is selected

You can change your variable to:

SET vDate = "=Max([Year])";

Maybe your QV app has a trigger to select one year on open (or only load one value for Year) and that makes your QV expression works.

Hope this helps.