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: 
Not applicable

How to use multiple selected months for calculating measures

Hey there,

I have a master calender and it is already possbile to select multiple months.

I also get the values with the GetFieldSelections() function. (output e.g: Jan,Feb,Mrz...)

But when I want to calculate different measues it is only working with one selected month.

My expression for calculating Retail Sales:

if(isNull(MasterMonth), SUM({<Year_RS=MasterYear>}Retail_Sales),
SUM({<Year_RS=MasterYear,Month_RS=MasterMonth>}Retail_Sales))

Its working very well if only one month is selected but if there are multiple months the result is always the total of the selected year.

I think its because of the isnull(MasterMonth) but I don't know how to solve it

Any ideas?

12 Replies
Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

When you operate with fields outside of your aggregation functions, such as SUM(), you can only operate with a single available value of the field, otherwise multiple available values always render a NULL in response.

I think in your case, it is safe to simply use the second part of your expression:

SUM({<Year_RS=MasterYear,Month_RS=MasterMonth>}Retail_Sales)

When no months are selected, the Set Analysis modifier will return all available months.

Otherwise, you can change your conditions to check the number of selected values.

cheers,

Oleg Troyansky

www.masterssummit.com - take your QlikView skills to the next level!

Anonymous
Not applicable
Author

Try using this expression:

if(len(MasterMonth)=0, SUM({<Year_RS=MasterYear>}Retail_Sales),
SUM({<Year_RS=MasterYear,Month_RS=P(MasterMonth)>}Retail_Sales))

Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

This formula, unfortunately, has the same shortcomings as the original - it uses the field MasterMonth outside of any aggregations.

hic
Former Employee
Former Employee

You cannot have

     if(len(MasterMonth)=0, SUM(...

since the field reference MasterMonth isn't enclosed by an aggregation function.


See more on Use Aggregation Functions!

HIC

Not applicable
Author

I think I have a workaround:

if(sum(DISTINCT (MasterMonth)) = 78,SUM({<Year_RS=MasterYear>}Retail_Sales),
SUM({<Year_RS=MasterYear,Month_RS=MasterMonth>}Retail_Sales))

After a few tests it seems to work....

Any comments?

ramoncova06
Specialist III
Specialist III

if you have a date field use two variables to get the min and max of your current selection and then add them into you set analysis

sum({<MasterDate = {'>=$(=(vMinDate))<=$(=(vMaxDate))'}>}Retail_Sales)

Anonymous
Not applicable
Author

Oh ok, got it. Thanks Henric.

Not applicable
Author

There is no opportunity to use variables in Qlik Sense.

The only possibility is to create measures.

ramoncova06
Specialist III
Specialist III

my bad ...

Then just use the functions instead of the variables

sum({<MasterDate = {'>=$(=(Min(MasterDate )))<=$(=(Max(MasterDate )))'}>}Retail_Sales)