Adding 2 Set Analysis Expressions Together

rileymd88 — Thu, 31 Aug 2017 15:35:47 GMT

Hi All,

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

FY	Month	Month Name	Retail Sales	Target	Total Sales
FY1718	01/04/2017	Apr	4	0.10	10
FY1718	01/05/2017	May	4	0.10	12
FY1718	01/06/2017	Jun	3	0.10	12
FY1718	01/07/2017	Jul		0.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):

Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar
Retail Sales/Total Sales	Retail Sales/ Total Sales	Retail Sales/ Total Sales	Target	Target	Target	Target	Target	Target	Target	Target	Target

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 Name	Combined
Apr	40%
May	33%
Jun	25%
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,

Re: Adding 2 Set Analysis Expressions Together

sunny_talwar — Thu, 31 Aug 2017 15:43:31 GMT

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

topic Adding 2 Set Analysis Expressions Together in QlikView

Adding 2 Set Analysis Expressions Together

Re: Adding 2 Set Analysis Expressions Together