Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
In pivot table, you need to write like this
Sum(aggr(your expression, your chart dimension 1, 2..))
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.
you need to used set analysis expression
try this
Sum({<UNIT= {'PC'}>}QTY* U.WT) + Sum({<UNIT= {'KG'}>}QTY)
Thanks buddy, its works really. Well done.
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).
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
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
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
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.