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

Announcements
Join us in Toronto Sept 9th for Qlik's AI Reality Tour! Register Now
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)%'))))))))))))))))))))))))))))