Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
All,
I'm hoping someone can point me in the right direction, I want to do a calculation on a pivot table to work out the percentage for each item, so I need to get the value for the total of the row... i.e.
Product A Total: 4000
When expanded, you see the subtotal for each sub-product:
P1 2000
P2 1000
P3 1000
So my calculation would be:
P1 4000/2000
P2 4000/1000
P3 4000/1000
I've tried TOTAL, but I don't want the total for all of the products, just the product related to the sub-product.. Hope this makes sense!
Kind Regards,
Dayna
Can you elaborate your question??
As per my understanding you want the percentage of a product from total.
So, your calculation should be:
sum(amount received) / sum(total amount received).
Regards
Neetu SIngh
Hi,
Use group in the TOTAL
Sum(Sales)/Sum(TOTAL <SubProducts> Sales)
Here you go
Neetu, by using TOTAL - I get the total for all products, but what I need is the sub total for each product for the sub-products, so unfortunately that doesn't work.
I tried using the group in the total, and I get the below - should the expression for Part 4054X be 3437?
Thanks,
Dayna
qlikuser14,
Thank you for your post, but I use set analysis in my calculation - so I don't think I can use AGGR - and I need it in one column.. Do you have any other suggestions?
Many thanks,
Dayna
You can do it in one column, I just gave you all the formulas in different columns so you can understand it. What do you mean you use set analysis? It would be the same... If you give me an example I tell you how to do it.
Hi Dayna,
you can use this
(sum(amount received) / aggr((sum(amount received)),sub product) )
Hi Dayna,
Aggr function will work with Set Analysis, as i am also using set analysis & aggr function in a single expression as mention below:
sum(aggr(sum({<Store={"=rank(sum(Sales))<3"}>} Sales),Store,Customer)).
Regards
Neetu Singh
O great, I'll give it a go! For some reason, I thought you couldn't use Set Analysis and Aggr together.. (If you can, then great!)
For the record, my calculation is:
sum
={'$(=date(addmonths(today(),-1),'MMM YYYY'))'},CV_tr_type = {'ISS-WO'}>} CV_tr_qty_loc)
Kind Regards,
Dayna