Help with Set analysis!

[#1]

Hi to all,

I'm new to the QV Community and I need help with set analysis!

I need to have a pivot table like this that calculate the % of the sales for each month for each year for each country:

Eg data is this:

Date	Country	Sales
11/01/2014	USA	100
22/05/2014	USA	400
21/06/2014	USA	500
01/05/2015	USA	300
22/06/2015	USA	700
15/01/2014	UK	4500
22/10/2014	UK	5500
14/04/2015	UK	8500
18/05/2015	UK	1500

I calculated field year and month in the script with a master calendar.

I need to have the pivot table like this if I select Year 2014:

Month	USA	UK
Jan	10,00%	45,00%
May	40,00%	0,00%
Jun	50,00%	0,00%
Oct	0,00%	55,00%

My problem that I have many problem with set analysis syntax.

I dont know how can I calculate the % of the sum of the month in the year (eg 2014 USA total sales is 1.000 and UK is 10.000, so I need to do eg. total USA Jan 2014/Totale USA 2014).

Which is the set analysis?

Please can you help me?

Thank you!

Best regards.

[sunny_talwar · Accepted solution]

This?

PFA

Best,

Sunny

[nsm1234567]

Hi There,

If I understand your question correctly, you don't need set analysis to accomplish what you're looking for.  Could you try an expression as follows:

=Sum(Sales)/Sum(TOTAL <Country> Sales)

[spsrk_84]

Hi ,

Please see the attached file. Hope the solution i provided is what you are expecting.

No need of set analysis in this case , Check & let me know of any issues.

Thanks,

Shiva !

[#5]

Hi,

This will help you!!

Thanks,

Shekar!!

[#6]

Hi,

thank you to all!

Best regards.