How to Reference the Expression Total value in a different Expression?

[#1]

I am trying to calculate "Share of Profit", the percent of Total Profit contributed by a particular Product (my dimension). I have many expressions that calculate each part of the Profit formula. In the end, I want to calculate (Profit from that Product) / (Profit total).

I know I can reference other expressions by typing the name of the column, but is there a way to reference the Total Expression value?  I was hoping [Profit].total or [Total Profit] or sum([Profit]) would work, but that doesn't seem to be working.

I've attaced a simplified example in Excel to show what I'm doing. What is the easiest way of typing the formula for the yellow cells, without copying all expressions in the different Expense steps?

Thanks for any help in advance!

[chematos]

Hi,

Did you try to use All ?

sum(All Profit)

[#3]

I tried, but that didn't work. The complete expression I tried was =Profit/sum(All Profit)

[chematos]

Sum({<Product = {'A'}>} Profit)/sum(All Profit)

[#5]

I don't have a problem with the numerator; =Profit is ok.

The problem is the denomenator, sum(All Profit).

[chematos]

So you should do all the additions and subtractions by Product to arrive at the partial result of the Profit.

Each Expense is a field?Profit is a Field or a variable?Maybe you must create a variable and store the result of the operation in that variable so you could use this value but if you don´t have a Field where is the Profit, you can´t use it like you want.

vProfitA = Sum({<Product = {'A'}>} Revenue) - Sum({<Product = {'A'}>} [Expense 1]) +

Sum({<Product = {'A'}>} [Expense 2]) + Sum({<Product = {'A'}>} [Expense 3]) + Sum({<Product = {'A'}>} [Expense 4])

/sum(vProfitTotal)

Do the same for all your products and store the total addition in vProfitTotal to get the total profit.

I hope this helps, I don´t know if is this what you need. If you had a field with the profits you mustn´t have problems with sum(all Profit)

If you have a table like

Product        Profit

A               39

B                      8

...

Sum({<Product = {'A'}>} Profit)/sum(All Profit) must return to you:      39/(39+8+...)

Regards

[#7]

Dimension: Product

Expression: Revenue, Expense 1, Expense 2, ...., Total Expense, Profit, Share of Profit

No variables in my example, although the Expense 1 and Expense 2 functions have variables.

The formulas for Expense 1 and Expense 2 are quite complicated set analysis for QV. I tried recreating the Share of Profit formula by copying each expression in the profit formula  and used the total function to get at Share of Profit...

     sum(  profit formula  ) / sum(total profit formula  ) .

This was not calculating exactly correctly, in part due to the complexity of how Expense 1 and Expense 2 are being calculated. Maybe I can try using all instead of total. What is the difference? QV help doesn't have ALL in its index.

[chematos]

Total takes into account the actual selection and All makes an addition of all values of the field no matters selections or dimensions.

I think you could get the same result with TOTAL and ALL. May be you should take a look about aggregation functions. It´s used for advanced aggregations. I think it can be usefull so the result of the function aggr() returns something like a temporary table. There is better documentation in the community and in the help of QV.

This discussion could be interesting for your case.

http://community.qlik.com/message/164389#164389

[#9]

Good to know about All.

I've used aggr() before and don't think that will help. Just wish there is a quick and easy way to reference that big black bold total number that is right there on the screen...

I will try putting in each expense expression and try to more manually calculate Share of Profit again, with no shortcut, which might work, but is not desirable.

If you have any other ideas, though, I'd appreciate. Thanks for all your time Jose.

[jonathandienst]

Hi

Ba careful with All, it has been deprecated by QV and may not be supported in future versions. The correct syntax is {1} TOTAL, as in:

     Sum(All Profit)

     Sum({1} TOTAL Profit)

"All" will work for now but I suggest that you avoid building obsolesence into your work!

Regards

Jonathan