Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
ankitg4688
Contributor III
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
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
ankitg4688
Contributor III
Contributor III
Author

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
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
ankitg4688
Contributor III
Contributor III
Author

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
Master III
Master III

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

Qlikview Parameterised Variables

petter
Partner - Champion III
Partner - Champion III

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.