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

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

Mon, 23 Apr 2012 18:20:01 GMT

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!

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

chematos — Mon, 23 Apr 2012 18:49:47 GMT

Hi,

Did you try to use All ?

sum(All Profit)

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

Mon, 23 Apr 2012 18:59:55 GMT

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

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

chematos — Mon, 23 Apr 2012 19:06:21 GMT

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

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

Mon, 23 Apr 2012 19:32:31 GMT

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

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

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

chematos — Mon, 23 Apr 2012 19:58:53 GMT

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

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

Mon, 23 Apr 2012 20:17:46 GMT

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.

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

chematos — Mon, 23 Apr 2012 20:44:29 GMT

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

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

Mon, 23 Apr 2012 20:49:17 GMT

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.

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

jonathandienst — Mon, 23 Apr 2012 21:03:52 GMT

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

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

Tue, 24 Apr 2012 12:57:26 GMT

Jonathan, thanks for the note on All vs. {1} TOTAL. That's not the question in this thread, but is good to know. The question is, can you reference the Total value on an Expression in a different Expression in an easy way?

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

Tue, 24 Apr 2012 13:15:50 GMT

I figured out what I needed to do. For Profit Share, copy the same expression that is in Profit. Then, on the Expression tab next to check the Relative box (right below the Enable check box). This will calculate % of Total.

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

Tue, 24 Apr 2012 13:24:01 GMT

You can also do it differently. Use sum(Profit) / sum(TOTAL Profit) and then go to the number format and click show in %. Check the attached sample file.

Regards,

Sajeevan