Announcements
Sept. 27, 10AM ET Do more with Qlik, Insight Advisor – our intelligent AI-assistant in Qlik Sense: Register
cancel
Showing results for
Did you mean:
Contributor

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

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.

Labels (2)

• ### Layout & Visualizations

1 Solution

Accepted Solutions
Specialist

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:
autonumber(Account) as SortOrder,
Balance as Balance_New,
Balance_LY as Balance_LY_New,
Diff as Diff_New,
Diff% as Diff%_New;
[
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]:
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]:
(Balance_New / Balance_LY_New)-1 as Diff%_New;
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]:
(Balance_New / Balance_LY_New)-1 as Diff%_New;
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,

5 Replies
Specialist

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,

Contributor
Author

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

Specialist

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:
autonumber(Account) as SortOrder,
Balance as Balance_New,
Balance_LY as Balance_LY_New,
Diff as Diff_New,
Diff% as Diff%_New;
[
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]:
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]:
(Balance_New / Balance_LY_New)-1 as Diff%_New;
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]:
(Balance_New / Balance_LY_New)-1 as Diff%_New;
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,

Contributor
Author

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?

Specialist

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,