topic Sub total between rows in Financial tables (in Pivot tables) in QlikView

Sub total between rows in Financial tables (in Pivot tables)

muratakkemik — Mon, 26 Jan 2026 18:19:17 GMT

I have an excel sheet which i'm trying to take P&L Setup and B/S setup. I can bring the all accounts with interval match but I could not bring the "sub total" lines according to between RowFrom and RowTo columns. I'm adding my Excel file.

I'm using in my expression but it does not help, can some one send me a sample?

if(RowType = 'Regular',Sum({<[Fiscal Year]={$(vYTDYear)} >} Amount),
if(RowType = 'Sum',aggr(Sum({<[Fiscal Year]={$(vYTDYear)}, RowNo= {"<=RowTo"}>}Amount), ReportRowGroup)
))

Regular part is coming but Sum parts are not coming? Please advise

Re: Sub total between rows in Financial tables (in Pivot tables)

alex00321 — Thu, 23 Jan 2020 11:59:24 GMT

Hi this is a topic I interested, and I took some time to research. The way I resolve it is kind of "Manuel". Without total, seems we could not make it. I use total to create first 2 "Sum" dimensions, you could see if this helps. Thanks!

Re: Sub total between rows in Financial tables (in Pivot tables)

muratakkemik — Thu, 23 Jan 2020 13:57:37 GMT

is there way for having subtotals according to row numbers? For in my example, 1st subtotal is calculated row 1 to 10, second one 1 to 12 ....? I saw this solution in somewhere but i could not find it again ;(

Re: Sub total between rows in Financial tables (in Pivot tables)

muratakkemik — Thu, 23 Jan 2020 14:22:25 GMT

I added 3rd one in red color (Net Revenue) but it did not appear on screen, am I wrong some where?

if(RowType = 'Regular',Sum(Amount),
if(RowType = 'Sum',
Pick(Match(ReportRowGroup,'Gross Revenue', 'Net Revenue', 'Net Revenue'),
Sum(TOTAL {<ReportRowGroup={'Revenue'}>}Amount),
Sum(TOTAL {<ReportRowGroup={'Discount (Given)'}>}Amount),
Sum(TOTAL {<ReportRowGroup={'Revenue'}, ReportRowGroup={'Discount (Given)'}>}Amount)
,1)))

Re: Sub total between rows in Financial tables (in Pivot tables)

alex00321 — Fri, 24 Jan 2020 01:32:25 GMT

Hi you could not put two identical value in Match part, how you could set the value for it? Thanks!

Re: Sub total between rows in Financial tables (in Pivot tables)

muratakkemik — Wed, 29 Jan 2020 10:11:24 GMT

I can solve the problem like below.

I put all subtotal formulas in variables then i used below formula in chart expression;

if(RowType = 'Regular',Sum(Amount),
if(RowType = 'Sum',
Pick(Match(ReportRowGroup,'Gross Revenue','Net Revenue', 'Gross Profit I', 'Gross Profit II', 'EBITDA', 'EBIT', 'EBT', 'Net Profit'),
$(vPL_GrossRevenue),
$(vPL_NetRevenue),
$(vPL_GrossProfit1),
$(vPL_GrossProfit2),
$(vPL_Ebitda),
$(vPL_Ebit),
$(vPL_Ebt),
$(vPL_NetProfit)
,1)))

I'm also adding here one of variable formula too;

for vPL_GrossRevenue , =Sum(TOTAL {<ReportRowGroup={'Revenue'}>}Amount)

for $(vPL_NetRevenue), =Sum(TOTAL {<ReportRowGroup={'Discount (Given)'}>}Amount) + $(vPL_GrossRevenue)

For $(vPL_GrossProfit1), =Sum(TOTAL {<ReportRowGroup={'COGS'}>}Amount) + $(vPL_NetRevenue)

....