Set Analysis on variable

[ankitg4688]

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.

[kaushiknsolanki]

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

[ankitg4688]

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))

[kaushiknsolanki]

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

[ankitg4688]

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.

[sasiparupudi1]

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

Qlikview Parameterised Variables

[petter]

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.