Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

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

Re: How to use multiple selected months for calculating measures

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!

sreekanth101
New Contributor

Re: How to use multiple selected months for calculating measures

Try using this expression:

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

MVP
MVP

Re: How to use multiple selected months for calculating measures

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

Employee
Employee

Re: How to use multiple selected months for calculating measures

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

Re: How to use multiple selected months for calculating measures

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

Re: How to use multiple selected months for calculating measures

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)

sreekanth101
New Contributor

Re: How to use multiple selected months for calculating measures

Oh ok, got it. Thanks Henric.

Not applicable

Re: How to use multiple selected months for calculating measures

There is no opportunity to use variables in Qlik Sense.

The only possibility is to create measures.

ramoncova06
Valued Contributor III

Re: How to use multiple selected months for calculating measures

my bad ...

Then just use the functions instead of the variables

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




Community Browser