
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Grand Total not showing the result on Pivot table
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.
- « Previous Replies
-
- 1
- 2
- Next Replies »
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
In pivot table, you need to write like this
Sum(aggr(your expression, your chart dimension 1, 2..))

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
you need to used set analysis expression
try this
Sum({<UNIT= {'PC'}>}QTY* U.WT) + Sum({<UNIT= {'KG'}>}QTY)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks buddy, its works really. Well done.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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).

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.

- « Previous Replies
-
- 1
- 2
- Next Replies »