Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
ankitg4688
New Contributor III

Set Analysis on variable

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.

6 Replies

Re: Set Analysis on variable

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
New Contributor III

Re: Set Analysis on variable

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

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
New Contributor III

Re: Set Analysis on variable

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
Honored Contributor III

Re: Set Analysis on variable

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

Qlikview Parameterised Variables

MVP
MVP

Re: Set Analysis on variable

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.