topic Re: P&L Ratio in QlikView

P&L Ratio

upaliwije — Mon, 08 Sep 2014 06:21:01 GMT

I have prepared the following P&L in my Pivot Table

I have prepared only the 1st three columns and want calculate the two Ratio columns highlighted in yellow.

Formula for calculation all Group Description Rows(except) are divided by Sales Row.In other words I want to show the ratio against Sales.

Pls help me to write the expression for the same

tresB — Mon, 08 Sep 2014 06:30:39 GMT

Try like:

Act_Ratio=Sum({<GROUP_DESCRIPTION={'SALES'}>}ACTUAL)/Sum(ACTUAL)*100

Bud_Ratio=Sum(BUDGET)*100/Sum({<GROUP_DESCRIPTION={'SALES'}>}BUDGET)

upaliwije — Mon, 08 Sep 2014 06:39:22 GMT

Thanks for your reply.

But in the results 100 is shown only against Sales Row Other rows remain blank

tresB — Mon, 08 Sep 2014 06:41:59 GMT

I guess field reference is not being right(pivot table), could you post a qvw with sample data?

Update: Or try with bit of correction like:

=Sum(ACTUAL)*100/Sum(total {<GROUP_DESCRIPTION={'SALES'}>}ACTUAL)

=Sum(BUDGET)*100/Sum(total {<GROUP_DESCRIPTION={'SALES'}>}BUDGET)

upaliwije — Mon, 08 Sep 2014 06:48:12 GMT

I am attaching sample P&L

Anonymous — Mon, 08 Sep 2014 06:49:03 GMT

PFA

Hope this is your req

upaliwije — Mon, 08 Sep 2014 07:00:03 GMT

Thanks both of you