topic Re: Pivot Problem: Calc percentages on upper dimension totals in QlikView

Pivot Problem: Calc percentages on upper dimension totals

hugmarcel — Wed, 29 Jan 2014 08:54:22 GMT

see test.qvw example attached, I do not manage to calculate percentages correctly, where the percentages depend on upper dimension totals.

Thx a lot for any suggestion.

Note that the underlying data model must not be changed!

Marcel

Re: Pivot Problem: Calc percentages on upper dimension totals

sujeetsingh — Wed, 29 Jan 2014 09:12:23 GMT

See the sample

Re: Pivot Problem: Calc percentages on upper dimension totals

Wed, 29 Jan 2014 09:33:33 GMT

Hi Marcel,

PFA...

Re: Pivot Problem: Calc percentages on upper dimension totals

Wed, 29 Jan 2014 10:05:19 GMT

Hi Marcel,

Please find the attached qvw where the % #Bookings/ #Calls are displayed in %.

Regards,

Varsha

Re: Pivot Problem: Calc percentages on upper dimension totals

hugmarcel — Wed, 29 Jan 2014 10:16:23 GMT

Your answer is wrong, If I have 1 Booking for PROD1 and 3 Calls for NAME N1, Result should be 1 / 3 = 33%.
33% must be written as number in the empty cell of the pivot next to PROD1.

Re: Pivot Problem: Calc percentages on upper dimension totals

hugmarcel — Wed, 29 Jan 2014 10:17:13 GMT

I do not see any change in your test.qvw... have uploaded the wrong file?

Hint: If I have 1 Booking for PROD1 and 3 Calls for NAME N1, Result should be 1 / 3 = 33%.

33% must be written as number in the empty cell of the pivot next to PROD1.

Re: Pivot Problem: Calc percentages on upper dimension totals

hugmarcel — Wed, 29 Jan 2014 10:17:28 GMT

Your answer is wrong, If I have 1 Booking for PROD1 and 3 Calls for NAME N1, Result should be 1 / 3 = 33%.

33% must be written as number in the empty cell of the pivot next to PROD1.

Re: Re: Pivot Problem: Calc percentages on upper dimension totals

Wed, 29 Jan 2014 13:09:35 GMT

Hi,

Can you check if this was what you expected??

I have attached a qvw.

Regards,

Varsha

Re: Pivot Problem: Calc percentages on upper dimension totals

kumarnatarajan — Wed, 29 Jan 2014 13:22:01 GMT

Hi,

Try the below syntex.

Ex: Num( sum(COUNT)/count(Distinct CALLID),'#,##0.00%')

Re: Pivot Problem: Calc percentages on upper dimension totals

hugmarcel — Wed, 29 Jan 2014 13:32:29 GMT

Thanks a lot! Yes, the lower dimensions levels are correctly calculated.

Hm, Some numbers for the upmost dimensions are missing. E.g. 18/7, 12 / 4. How to add these?

And is it possible to suppress the empty PLAN, TOPC, NAME and PROD lines and keep the calculations?

Thx a lot for your help! Marcel

Re: Pivot Problem: Calc percentages on upper dimension totals

hugmarcel — Wed, 29 Jan 2014 14:37:26 GMT

your formula is not correct, see file from Varsha Shashidhar 29.01.2014 08:09, very helpful and almost correct!

Marcel

Re: Pivot Problem: Calc percentages on upper dimension totals

Wed, 29 Jan 2014 14:44:52 GMT

try to suppress null values in dimensions tab

Re: Pivot Problem: Calc percentages on upper dimension totals

hugmarcel — Wed, 29 Jan 2014 14:50:03 GMT

Hi,

this will not work, as suppressing the NULL Products will suppress all call numbers.

However, I found a solution, based on usage of "dimensionality()" to use the correct formula depending on dimension level.

Thx a lot again for your help, and some greetings from Switzerland to a pretty supporter!

Marcel