topic Re: Set Expression Working in QlikView, But Not in Qlik Sense in QlikView

Set Expression Working in QlikView, But Not in Qlik Sense

asmithids — Wed, 30 Sep 2015 12:10:14 GMT

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.

Re: Set Expression Working in QlikView, But Not in Qlik Sense

mjm — Wed, 30 Sep 2015 13:06:32 GMT

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,

Re: Set Expression Working in QlikView, But Not in Qlik Sense

asmithids — Wed, 30 Sep 2015 13:37:40 GMT

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.

Re: Set Expression Working in QlikView, But Not in Qlik Sense

mjm — Wed, 30 Sep 2015 14:01:37 GMT

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 & previous week

Re: Set Expression Working in QlikView, But Not in Qlik Sense

asmithids — Wed, 30 Sep 2015 14:06:58 GMT

Hi Mark,

I think it will. Thank you again!

Re: Set Expression Working in QlikView, But Not in Qlik Sense

mjm — Wed, 30 Sep 2015 14:08:46 GMT

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

Thanks,

Re: Set Expression Working in QlikView, But Not in Qlik Sense

mjm — Wed, 30 Sep 2015 15:00:12 GMT

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

Thanks

Re: Set Expression Working in QlikView, But Not in Qlik Sense

rubenmarin — Wed, 30 Sep 2015 15:20:26 GMT

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.