topic Re: Help with Set analysis! in QlikView

Help with Set analysis!

Tue, 14 Jul 2015 17:05:41 GMT

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.

Re: Help with Set analysis!

sunny_talwar — Tue, 14 Jul 2015 19:59:03 GMT

This?

PFA

Best,

Sunny

Re: Help with Set analysis!

nsm1234567 — Tue, 14 Jul 2015 20:04:57 GMT

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)

Re: Help with Set analysis!

spsrk_84 — Tue, 14 Jul 2015 20:21:00 GMT

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 !

Re: Help with Set analysis!

Tue, 14 Jul 2015 20:25:33 GMT

Hi,

This will help you!!

Thanks,

Shekar!!

Re: Help with Set analysis!

Wed, 15 Jul 2015 07:39:36 GMT

Hi,

thank you to all!

Best regards.