topic Re: Set Analysis on variable in QlikView

Set Analysis on variable

ankitg4688 — Thu, 10 Aug 2017 19:25:31 GMT

Set analysis has Identifier, Operator and Modifier.

E.g.

sum({1-$<Month= {'January'}>} Sales)

Is it possible to pass a calculated value of Sales like in variable $(Sales) ?

In that case the set expressions will be

=sum({1-$<Month= {'January'}>} $(Sales))

Please suggest. Thanks.

Re: Set Analysis on variable

kaushiknsolanki — Thu, 10 Aug 2017 19:29:27 GMT

Do you mean you want to dynamically set the Field Name?

If so then your expression is right.

=sum({1-$<Month= {'January'}>} $(Sales))

Assuming Sales Variable will have a Sales Field name.

Regards,

Kaushik Solanki

Re: Set Analysis on variable

ankitg4688 — Thu, 10 Aug 2017 19:39:28 GMT

Yes Kaushik. Sales variable will have calculated value coming from set expressions like

Sales = sum({1-$<Week= {1}>} Sales) + sum({1-$<Week= {2}>} Sales) - sum({1-$<Week= {3}>} Sales)

and then,

=sum({1-$<Month= {'January'}>} $(Sales))

Re: Set Analysis on variable

kaushiknsolanki — Thu, 10 Aug 2017 19:41:45 GMT

Hi,

It means it is not going to be a field.

I doubt you can use the expression as you have shown.

Kindly share the use case with us, like what you are trying to achieve, that will help to understand in detail.

Regards,

Kaushik Solanki

Re: Set Analysis on variable

ankitg4688 — Thu, 10 Aug 2017 20:10:03 GMT

Thanks for your help. My real scenario is different but I am trying for above approach to achieve it.

I have to calculate 13 months rolling data as per calendar Year_Month and I have 5 years of data. Users will have year and Year_Month for selection. I have 3 date columns.

1. Item Start date

2. Item End date

3. Master Calendar date

Formula for line chart -

count(Items) as per Start_date - count(items) as per end date + count(New Items) in every month. This data should be shown across 13 rolling months.

Re: Set Analysis on variable

sasiparupudi1 — Sat, 12 Aug 2017 08:24:09 GMT

May be have a look at the following post if it can be of anu use to you

Qlikview Parameterised Variables

Re: Set Analysis on variable

petter — Sat, 12 Aug 2017 13:07:37 GMT

I would prefer to extend the Master Calendar with an additional field that is the relative month. You can name this field RelMonth and the current month would be 0, the previous month would be -1 and one year ago would be -12 and so forth.

Then creating a sum of the last 13 months would look like this:

Sum( {<RelMonth={0,-1,-2,-3,-4,-5,-6,-7,-8,-9,-10,-11,-12}>} Sales)

You could also put the value set into a variable by having a variable named

vRelMonths = '0,-1,-2,-3,-4,-5,-6,-7,-8,-9,-10,-11,-12';

and the expression could use this like this:

Sum( {<RelMonth={$(vRelMonths)}>} Sales)

The beauty of the last approach would be that you would be able to change the range of the rolling months dynamically in the UI if you want.