Grand Total not showing the result on Pivot table

[Anonymous]

I'm new to Qlik sense, I used to calculate the weight of the product based on two condition.

1. If unit of measure is Pcs then the Weight = Unit.Wt*Quantity

2. If unit of measure is Kg then the Weight = Quantity.

for this scenario, I used formula as below

if(UNIT='PC',SUM(U.Wt*QTY),If(UNIT='KG',SUM(QTY))). the result seems is Ok for the detail records, but it is not appearing for Grand total and appear as Null value.

Attached the screenshot for your review.

I need to know where is the issue which is not showing the Grand total either from my formula or any short of workaround.

[Anil_Babu_Samineni]

In pivot table, you need to write like this

Sum(aggr(your expression, your chart dimension 1, 2..))

[Anonymous]

Dear, thanks for your update.

I followed your instruction,but the result shows "Error in Expression". May be I'm not understanding clearly the syntax.

My Expression is below

if(UNIT='PC',SUM(Aggr(U_WT*QTY)),If(UNIT='KG',SUM(Aggr(QTY)))).

Please correct me if I'm wrong.

Thanks / Best Regards.

[woshua5550]

you need to used set analysis expression

try this

Sum({<UNIT= {'PC'}>}QTY* U.WT) + Sum({<UNIT= {'KG'}>}QTY)

[Anonymous]

Thanks buddy, its works really. Well done.

[oknotsen]

If your question is now answered, please flag the Correct Answer (via the big "Correct Answer" button near every post; not visible in preview) and Helpful Answers (found under the Actions menu under every post).

If not, please make clear what part of this topic you still need help with .

... and please don't mark your own posts as Helpful (removed).

[Anonymous]

Sorry, I don't find the Flag to Correct Answer, please guide me how to flag it.

In Action - found only "Mark As Helpfull"

Thanks

[Anonymous]

Dear, the Expression which you provided works perfectly. Thanks for that.

Further I need to calculate the Total Weight for the current Year Sales in order to show in KPI. So I used the below formula to calculate the Weight.

Sum( { $< [CUSTINVOICEJOUR.INVOICEDATE.autoCalendar.InYTD]={1} ,[CUSTINVOICEJOUR.INVOICEDATE.autoCalendar.YearsAgo]={0} > } [QTY]*U.WT).

Could you please update me the right expression using your suggested expression  Sum({<UNIT= {'PC'}>}QTY* U.WT) + Sum({<UNIT= {'KG'}>}QTY) to calculate the Current Year Sold Kgs.

Thanks

[woshua5550]

guess you mean that the result also depends on "UNIT" and this expression you provided is for "PC" , but you need it also apply for "KG"? maybe like this:

IF(UNIT = "PC",

Sum( { $< [CUSTINVOICEJOUR.INVOICEDATE.autoCalendar.InYTD]={1} ,[CUSTINVOICEJOUR.INVOICEDATE.autoCalendar.YearsAgo]={0} > } [QTY]*U.WT),

Sum( { $< [CUSTINVOICEJOUR.INVOICEDATE.autoCalendar.InYTD]={1} ,[CUSTINVOICEJOUR.INVOICEDATE.autoCalendar.YearsAgo]={0} > } [QTY]))

btw , you need click on your question title so that you can find "Correct Answer" button

[Anonymous]

Dear, I tried with your formula as below, but I'm getting an expression error "Bad Field name "Kg"". if I change to single quote 'KG', expression is OK, but the value is not matching with the current Year result and showing big amount (67.1M) instead of (18.2M).

IF(UNIT = 'KG',

Sum( { $< [CUSTINVOICEJOUR.INVOICEDATE.autoCalendar.InYTD]={1} ,[CUSTINVOICEJOUR.INVOICEDATE.autoCalendar.YearsAgo]={0} > }  [CUSTINVOICETRANS.QTY]*FACTOR),

Sum( { $< [CUSTINVOICEJOUR.INVOICEDATE.autoCalendar.InYTD]={1} ,[CUSTINVOICEJOUR.INVOICEDATE.autoCalendar.YearsAgo]={0} > } [CUSTINVOICETRANS.QTY]))

Need to calculate the for Current Year with the condition based on the UNIT.