Announcements
Sept. 27, 10AM ET Do more with Qlik, Insight Advisor – our intelligent AI-assistant in Qlik Sense: Register
cancel
Showing results for
Did you mean:
Creator III

Division of two values from same column

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!!

1 Solution

Accepted Solutions
MVP

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)

8 Replies
Creator III
Author

Any pointers you could provide?

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Champion III

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

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
MVP

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)

Creator III
Author

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.

Creator III
Author

Hi Anil,

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

MVP

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

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

Creator III
Author

Ah, i missed it.

Perfect this works like charm!!

Thanks Much.

Have a great weekend.

Community Browser