topic Re: Split Sales Using Total In Set Analysis in QlikView

Split Sales Using Total In Set Analysis

Mon, 30 May 2016 15:18:08 GMT

I have a data set as given below.

ID	Product	Year	Semester	Sales	Target
1	-	2013	1H	100	-
1	-	2014	1H	100	-
1	-	2015	1H	100	-
2	-	2013	2H	100	-
2	-	2014	2H	100	-
2	-	2015	2H	100	-
-	X	2013	1H	-	90
-	X	2014	1H	-	90
-	X	2015	1H	-	90
-	Y	2013	1H	-	100
-	Y	2014	1H	-	200
-	Y	2015	1H	-	120
-	X	2013	2H	-	90
-	X	2014	2H	-	90
-	X	2015	2H	-	90
-	Y	2013	2H	-	150
-	Y	2014	2H	-	250
-	Y	2015	2H	-	140

I want to show it in the below format. But the values are not as expected. Targets should be only for product Y and split according to the Year-Semester. Currently is show sum of all targets for product Y. I am using the expression sum({<Product={'Y'}>}total distinct Target)

Year	2013	2013	2013	2013	2014	2014	2014	2014	2015	2015	2015	2015
Semester	1H	1H	2H	2H	1H	1H	2H	2H	1H	1H	2H	2H
ID	Sales	Target	Sales	Target	Sales	Target	Sales	Target	Sales	Target	Sales	Target
1	100	960	0	960	100	960	0	960	100	960	0	960
2	0	960	100	960	0	960	100	960	0	960	100	960
-	0	960	0	960	0	960	0	960	0	960	0	960

Re: Split Sales Using Total In Set Analysis

sunny_talwar — Mon, 30 May 2016 15:20:23 GMT

Do you have an application where the data is already loaded into the application to play around with it?

Re: Split Sales Using Total In Set Analysis

marcus_sommer — Mon, 30 May 2016 15:21:38 GMT

Should your expression not look like that: sum({<Product={'Y'}>} Target) without total and distinct?

- Marcus

Re: Split Sales Using Total In Set Analysis

sunny_talwar — Mon, 30 May 2016 15:34:52 GMT

Or this:

sum({<Product={'Y'}>}total <Semester, Year> distinct Target)