Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Pooja92
Contributor
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.

Pooja92_0-1684925672058.png

 

Labels (2)
1 Solution

Accepted Solutions
Aditya_Chitale
Specialist
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:
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:

Aditya_Chitale_0-1685019748341.png

 

Regards,

Aditya

View solution in original post

5 Replies
Aditya_Chitale
Specialist
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,

Aditya

Pooja92
Contributor
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

Aditya_Chitale
Specialist
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:
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:

Aditya_Chitale_0-1685019748341.png

 

Regards,

Aditya

Pooja92
Contributor
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?

Aditya_Chitale
Specialist
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

Aditya_Chitale_0-1685091114880.png

 

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