Split Sales Using Total In Set Analysis

[#1]

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

[sunny_talwar]

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

[marcus_sommer]

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

- Marcus

[sunny_talwar]

Or this:

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