topic Re: Division of two values from same column in QlikView

Division of two values from same column

psk180590 — Fri, 20 Jan 2017 09:33:17 GMT

Hello All,

I have data as follows:

Market Product Sales Units

AA 1 100 5

AA 2 130 8

AA 500 50

When there are no entries in the Product column it indicates the values are for the whole market.

Ex: in the above data the Sales for whole market AA is 500 and the units are 50.

I need to display this in a Pivot table with all the dimensions and the percentage of Sales and Units out of the whole market.

So, here when the user wants to see for Product 1 it has to be 100/500 = 20% and for Product 2 130/500 = 26%

But, in the Product column there should be no blank space as in the table or any other value. It should only display the Valid products (1 2 here).

TIA!!

Re: Division of two values from same column

psk180590 — Fri, 20 Jan 2017 09:48:18 GMT

gwassenaar‌ stalwar1‌

Any pointers you could provide?

Re: Division of two values from same column

Anil_Babu_Samineni — Fri, 20 Jan 2017 09:50:05 GMT

From given table, What was the expected table to see?

Re: Division of two values from same column

vinieme12 — Fri, 20 Jan 2017 09:52:00 GMT

SCRIPT

//////////////////////////////////////////////////////

Load Market,if(len(Product)=0,'ISTOTAL',Product) as Product,Sales,Units Inline [
Market,Product,Sales,Units
AA,1,100,5
AA,2,130,8
AA,,500,50
];
//////////////////////////////////////////////////////End of Script //////////////////////////////////////////////////////

Chart

dimension: MArket , Product

Expressions

sum(Sales) / Sum({<Product = {'ISTOTAL'}>}TOTAL <Market> Sales)

sum({<Product -= {'ISTOTAL'}>}Sales)

Re: Division of two values from same column

sunny_talwar — Fri, 20 Jan 2017 10:12:20 GMT

I think Vineeth‌ to hide blank column, would you need to add {<Product -= {'ISTOTAL'}>} to your 1st expression also?

Sum({<Product -= {'ISTOTAL'}>}Sales) / Sum({<Product = {'ISTOTAL'}>}TOTAL <Market> Sales)

Re: Division of two values from same column

psk180590 — Fri, 20 Jan 2017 10:26:09 GMT

stalwar1‌ vinieme12‌

Thanks both for your quick suggestions!!

The first SS is based on stalwar1‌'s suggestion and the second on vinieme12‌'s suggestion.

But, the problem is i don't want 'ISTOTAL' under Product in my pivot table. Only want to display 1 & 2.

Re: Division of two values from same column

psk180590 — Fri, 20 Jan 2017 10:27:20 GMT

Hi Anil,

You can see the expected result in the below attached SS.

Re: Division of two values from same column

sunny_talwar — Fri, 20 Jan 2017 10:27:51 GMT

Did you put a small negative sign in front of the equal sign?

Sum({<Product -= {'ISTOTAL'}>}Sales) / Sum({<Product = {'ISTOTAL'}>}TOTAL <Market> Sales)

Re: Division of two values from same column

psk180590 — Fri, 20 Jan 2017 10:32:28 GMT

Ah, i missed it.

Perfect this works like charm!!

Thanks Much.

Have a great weekend.