Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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!
Try using this expression:
if(len(MasterMonth)=0, SUM({<Year_RS=MasterYear>}Retail_Sales),
SUM({<Year_RS=MasterYear,Month_RS=P(MasterMonth)>}Retail_Sales))
This formula, unfortunately, has the same shortcomings as the original - it uses the field MasterMonth outside of any aggregations.
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
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?
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)
Oh ok, got it. Thanks Henric.
There is no opportunity to use variables in Qlik Sense.
The only possibility is to create measures.
my bad ...
Then just use the functions instead of the variables
sum({<MasterDate = {'>=$(=(Min(MasterDate )))<=$(=(Max(MasterDate )))'}>}Retail_Sales)