Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
rileymd88
Contributor III
Contributor III

Adding 2 Set Analysis Expressions Together

Hi All,

I have a very simple application with one table of data in the following format:

   

FYMonthMonth NameRetail SalesTargetTotal Sales
FY171801/04/2017Apr40.1010
FY171801/05/2017May40.1012
FY171801/06/2017Jun30.1012
FY171801/07/2017Jul0.10

As you can see from the data sample above, I have real figures for Retail Sales and Total Sales up until 01/06/2017 and from that point onwards I only have a target. I have put the latest month with sales data as a variable called var_month = '01/06/2017'

I would like to show in a pivot table the following (with the actual number in percent): 

AprMayJunJulAugSepOctNovDecJanFebMar
Retail Sales/Total SalesRetail Sales/ Total SalesRetail Sales/ Total SalesTargetTargetTargetTargetTargetTargetTargetTargetTarget

   

I tried to acheive this by using the following Expression:

(Sum({<FY ={'FY1718'}, [Month]={"<=$(=$(var_month))"}>}[Retail Sales]) / Sum({<FY ={'FY1718'}, [Month]={"<=$(=$(var_month))"}>}[Total Sales]))
+
Sum({<FY ={'FY1718'}, [Month]={">$(=$(var_month))"}>}Target)

I get the following pivot table when using this Expression:

   

Month NameCombined
Apr40%
May33%
Jun25%
Jul-
Aug-
Sep-
Oct-
Nov-
Dec-
Jan-
Feb-
Mar-

   

Strangly enough, each indivudal set analysis expression works fine by itself.

I have attached a sample application to help further debugging

Thanks,

R

1 Solution

Accepted Solutions
sunny_talwar

Try this

RangeSum(

(Sum({<FY ={'FY1718'}, [Month]={"<=$(=$(var_month))"}>}[Retail Sales]) / Sum({<FY ={'FY1718'}, [Month]={"<=$(=$(var_month))"}>}[Total Sales])),

Sum({<FY ={'FY1718'}, [Month]={">$(=$(var_month))"}>}Target))

View solution in original post

1 Reply
sunny_talwar

Try this

RangeSum(

(Sum({<FY ={'FY1718'}, [Month]={"<=$(=$(var_month))"}>}[Retail Sales]) / Sum({<FY ={'FY1718'}, [Month]={"<=$(=$(var_month))"}>}[Total Sales])),

Sum({<FY ={'FY1718'}, [Month]={">$(=$(var_month))"}>}Target))