Is there a way to add/define subtotals at different levels in a P&L Pivot?

[Pooja92]

I have a P&L structure like below, but i dont want to pre define the values for subtotals such as "Net Sales revenue". Instead I want to define it as gross sales revenue - Less returns and allowances to be calculated dynamically.

[Aditya_Chitale]

What do you mean by predefined values for "Net Sales revenue" ?

Are these values from provided screenshot fixed ? Also what formula is used to calculate allowances if you want it to be calculated dynamically ?

Regards,

Aditya

[Pooja92]

In the above screenshot all the values are fixed/hardcoded.

In my scenario, values for 'gross sales revenue ' and 'Less returns and allowances' are fixed, 'Net Sales revenue' is a calculated field with the below formula 

Net Sales revenue=Gross sales revenue - Less returns and allowances

[Aditya_Chitale · Accepted solution]

Tried using sample Data. Grouped and subtracted "gross revenue - less allowance" in separate tables in backend for MTD & YTD.

Hope this is what you were looking for.  If this didn't help, please share sample data. You can jumble the numbers

Test:

load *,

autonumber(Account) as SortOrder,

    Balance as Balance_New,

    Balance_LY as Balance_LY_New,

    Diff as Diff_New,

    Diff% as Diff%_New;

load * Inline

[

Balance,    Balance_LY,    Diff,    Diff%,    Period_Flag,    Account               

2769.92,    2471.92,       298.00,  0.12,      MTD,            Gross_Sales_Revenue

28.83,      20.17,         8.67,    0.43,      MTD,            Less_Return_Allowance

33239.00,   29663.00,      3576.00, 0.12,      YTD,            Gross_Sales_Revenue

346.00,     242.00,        104.00,  0.43,      YTD,            Less_Return_Allowance

];

NoConcatenate

[Grouping]:

load

sum(Balance) as Balance, 

sum(Balance_LY) as Balance_LY, 

sum(Diff) as Diff, 

(sum(Balance)/sum(Balance_LY))-1 as Diff%,

    sum(SortOrder)  as SortOrder,

Period_Flag,

Account

resident Test group by Period_Flag,Account;

noConcatenate

[net sales revenue MTD]:

load *,

(Balance_New / Balance_LY_New)-1 as Diff%_New;

Load

Balance,

    Balance - peek(Balance) as Balance_New,

    Balance_LY,

    Balance_LY - peek(Balance_LY) as Balance_LY_New,

    Diff,

    Diff - peek(Diff) as Diff_New,

    Diff%,

    Period_Flag,

    SortOrder,

    'Net Sales Revenue' as Account

Resident [Grouping]

where Period_Flag='MTD' and

wildmatch(Account,'Gross_Sales_Revenue','Less_Return_Allowance') 

order by SortOrder desc;

Concatenate(Test)

final_MTD:

load * resident [net sales revenue MTD];

drop table [net sales revenue MTD];

noConcatenate

[net sales revenue YTD]:

load *,

(Balance_New / Balance_LY_New)-1 as Diff%_New;

Load

Balance,

    Balance - peek(Balance) as Balance_New,

    Balance_LY,

    Balance_LY - peek(Balance_LY) as Balance_LY_New,

    Diff,

    Diff - peek(Diff) as Diff_New,

    Diff%,

    Period_Flag,

    SortOrder,

    'Net Sales Revenue' as Account

Resident [Grouping]

where Period_Flag='YTD' and

wildmatch(Account,'Gross_Sales_Revenue','Less_Return_Allowance') 

order by SortOrder desc;

Concatenate(Test)

final_YTD:

load * resident [net sales revenue YTD];

drop table [net sales revenue YTD];

drop table [Grouping];

Output:

Regards,

Aditya

[Pooja92]

Thanks Aditya, this solution is working. Just one followup, what if we have more than one field to add /subtract?

Will  peek() work in that case also?

[Aditya_Chitale]

In that case, you will have to mention row number  in peek() function of the second/third field.

For eg:

 Balance - peek(Balance) - peek(Balance,-2) as Balance_New

If you want to learn how peek() works, here's the link of Qlik Support Article:

How Peek() function works - detailed explanation - Qlik Community - 1801695

If the solution helped, Please don't forget to mark it as a solution !

Regards,

Aditya