Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

madnanansari
New Contributor III

PNL Statement - % by Revenue calculation in Pivot table is wrong

I am working with Profit/Loss Statement. The first column shows the figures for the heads in PNL like Sales, Cost of Sales, GP, EBITDA etc. The second column is (First Column as a Percentage of Sales). 


If I am running my report for full year; the below formula will be the divider to bring the percentage:


Sum(Total {$<[PNL Head Name]={'Sales'}, [Year] = {"$(=Max(Year))"} >} [Account Balance])

If I use a table format in Qlik Sense this formula brings sales on each line; which can be used to divide the individual PNL heads to bring percentages.

This formula do not work when I use a pivot table and need a report by individual month. like Jan, Feb, Mar, ... etc in the column of the pivot. Infact it brings the total for the year in each month column which is wrong.

Can anybody guess how to resolve this issue?

Tags (1)
1 Solution

Accepted Solutions

Re: PNL Statement - % by Revenue calculation in Pivot table is wrong

Hi Mohammad, I suppose you have month as a vertical dimension and you want to divide for the value of each month.

In that case you need to limit the TOTAL to this field values, can you try?:

Sum(Total <Field_Used_As_Vertical_Dimension> {$<[PNL Head Name]={'Sales'}, [Year] = {"$(=Max(Year))"} >} [Account Balance])

2 Replies

Re: PNL Statement - % by Revenue calculation in Pivot table is wrong

Hi Mohammad, I suppose you have month as a vertical dimension and you want to divide for the value of each month.

In that case you need to limit the TOTAL to this field values, can you try?:

Sum(Total <Field_Used_As_Vertical_Dimension> {$<[PNL Head Name]={'Sales'}, [Year] = {"$(=Max(Year))"} >} [Account Balance])

madnanansari
New Contributor III

Re: PNL Statement - % by Revenue calculation in Pivot table is wrong

That's solved my issue. Thanks.