Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
cka
Contributor II
Contributor II

Total in Pivot table when it is open

The total in my Pivot table is wrong when it's open and correct when it's closed.

cka_0-1729069599492.png

cka_1-1729069620076.png

In a table it's okay

cka_2-1729069651610.png

How do you correct this?

 

Labels (2)
2 Replies
VBD
Partner - Creator II
Partner - Creator II

Hello, 

what is the formula used in your expression ?

Regards,

Valentin Billaud
Next Decision
cka
Contributor II
Contributor II
Author

the formula for the total "Operating Expenses" is :

num(SUM({$<[AccountCC5 Code]={OPEX},[Calendar Year]={$(=max([Calendar Year])-1)}, [Calendar Day Of Year] = { "<=$(=DayNumberOfYear(vDateRefLY))"}, 1=1>}[GL JE Line Balance $(vCurrency)] / vCurrencyDivider * [AccountCC Multiplier]) , vCurrencyFormat)

it is coming from a big one :

if(QBR_Dim2='Total Sales', $(vTotalSales4),
if(QBR_Dim2='Misc Discount', $(vMiscDiscount4),
if(QBR_Dim2='Commercial Net Sales', $(vCommercialNetSales4),
if(QBR_Dim2='Bonus and discounts granted', $(vBonusanddiscountsgrante4),
if(QBR_Dim2='Net Sales', $(vNetSales4),
if(QBR_Dim2='Net Sales %', num($(vNetSales4)/$(vTotalSales4),'#`##0.0%;(#`##0.0)%'),
if(QBR_Dim2='Cost of goods sold', $(vCostofgoodssold4),
if(QBR_Dim2='Cost of goods offered', $(vCostofgoodsoffered4),
if(QBR_Dim2='Other cogs', $(vOthercogs4),
if(QBR_Dim2='Absorption Difference', $(vAbsorptionDifference4),
if(QBR_Dim2='Variance', $(vVariance4),
if(QBR_Dim2='Gross Margin', $(vGrossMargin4),
if(QBR_Dim2='Gross Margin %', num($(vGrossMargin4)/$(vNetSales4),'#`##0.0%;(#`##0.0)%'),
if(QBR_Dim2='Personal Expenses', $(vPersonalExpenses4),
if(QBR_Dim2='Commercial Expenses', $(vCommercialExpenses4),
if(QBR_Dim2='Structural Costs', $(vStructuralCosts4),
if(QBR_Dim2='Personal Expenses %', num($(vPersonalExpenses4)/$(vNetSales4),'#`##0.0%;(#`##0.0)%'),
if(QBR_Dim2='Commercial Expenses %', num($(vCommercialExpenses4)/$(vNetSales4),'#`##0.0%;(#`##0.0)%'),
if(QBR_Dim2='Structural Costs %', num($(vStructuralCosts4)/$(vNetSales4),'#`##0.0%;(#`##0.0)%'),
if(QBR_Dim2='EBITDA', $(vEBITDA4),
if(QBR_Dim2='EBITDA %', num($(vEBITDA4)/$(vNetSales4),'#`##0.0%;(#`##0.0)%'),
if(QBR_Dim2='EBIT', $(vEBIT4),
if(QBR_Dim2='EBIT %', num($(vEBIT4)/$(vNetSales4),'#`##0.0%;(#`##0.0)%'),
if(QBR_Dim2='EBT', $(vEBT4),
if(QBR_Dim2='EBT %', num($(vEBT4)/$(vNetSales4),'#`##0.0%;(#`##0.0)%'),
if(QBR_Dim1='Operating Expenses', $(vQBR4),
if(QBR_Dim1='Operating Expenses %', num($(vOPEX4)/$(vNetSales4),'#`##0.0%;(#`##0.0)%'))))))))))))))))))))))))))))