Skip to main content
Announcements
Qlik Connect 2025: 3 days of full immersion in data, analytics, and AI. May 13-15 | Orlando, FL: Learn More
cancel
Showing results for 
Search instead for 
Did you mean: 
psk180590
Creator III
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
sunny_talwar

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)

View solution in original post

8 Replies
psk180590
Creator III
Creator III
Author

gwassenaarstalwar1

Any pointers you could provide?

Anil_Babu_Samineni

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

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
vinieme12
Champion III
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.
sunny_talwar

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)

psk180590
Creator III
Creator III
Author

stalwar1vinieme12

Thanks both for your quick suggestions!!

Sunny.JPGVineet.JPG

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.

psk180590
Creator III
Creator III
Author

Hi Anil,

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

sunny_talwar

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

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

psk180590
Creator III
Creator III
Author

Ah, i missed it.

Perfect this works like charm!!

Thanks Much.

Have a great weekend.