Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Skip the ticket, Chat with Qlik Support instead for instant assistance.
cancel
Showing results for 
Search instead for 
Did you mean: 
Hemachandran1
Contributor
Contributor

Hi Team ..I have a pivot table in that I am using if and aggr function..in the pivot table I am not getting correct overall total.please help on it. T

 
Labels (3)
3 Replies
Gabbar
Creator III
Creator III

Please Edit you question, add a sample data set, the current output, the expected output, 
The expression you are using, Currently we dont know anything about what you are doing.

Regards, Akash Dhandhi.
If this resolves your Query please like and accept this as an answer.
Hemachandran1
Contributor
Contributor
Author

Hi Gabbar,

Thanks for the reply. Please find the attached screenshot. Overall total value is not displaying correctly..

Hemachandran1_1-1685083610482.png

Total shows Wrong..

Expectation Totals need to be shown correct..

Expression Used in Pivot table :

if(Flag='Inventory',Num(sum({<Flag={'Inventory'},Month={"$(=Max(Month))"}>}[LY Actual])/vUnit,'#,##0'),
if(Flag='MAT Sales',
Num(if([Level 4 Description]='PH',

(sum(TOTAL <[Level 4 Description1],[Level 3 Description],[Level 6 Description1],[Level 5 Description1],[BG],[BU],[Business],[Business Model]>{$<[BG-C/MKT-C-MAT]={'World'},Flag={'MAT Sales'},Month={"$(=Max(Month))"},MonthL=>}aggr(sum({$<[BG-C/MKT-C-MAT]={'World'},Flag={'MAT Sales'},Month={"$(=Max(Month))"},MonthL=>}[LY Actual]),[Business Model],[Business],[BU],[BG],[BG-C/MKT-C-MAT],[Level 4 Description1],[Level 3 Description],[Level 5 Description1], [Level 6 Description1]))/vUnit),
//($(mktcformulalytoghsht1))
(sum(TOTAL<[BG],[BU],[Level 4 Description],[Business],[Business Model]>{$<[BG-C/MKT-C-MAT]={'BG-C','MKT-C'},Flag={'MAT Sales'},Month={"$(=Max(Month))"},MonthL=>}aggr(sum({$<[BG-C/MKT-C-MAT]={'BG-C','MKT-C'},Flag={'MAT Sales'},Month={"$(=Max(Month))"},MonthL=>}[LY Actual]),[BG-C/MKT-C-MAT],[Business],[Business Model],[BG],[BU],[Level 4 Description]))/vUnit)
),'#,##0'),
if(Flag='Inv/Sales',Num(sum({<FlagSource={'Inventory'},Month={"$(=Max(Month))"}>}[LY Actual])/vUnit
/(if([Level 4 Description]='PH',

(sum(TOTAL <[Level 4 Description1],[Level 3 Description],[Level 6 Description1],[Level 5 Description1],[BG],[BU],[Business],[Business Model]>{$<[BG-C/MKT-C-MAT]={'World'},FlagSource={'MAT Sales'},Month={"$(=Max(Month))"},MonthL=>}aggr(sum({$<[BG-C/MKT-C-MAT]={'World'},FlagSource={'MAT Sales'},Month={"$(=Max(Month))"},MonthL=>}[LY Actual]),[Business Model],[Business],[BU],[BG],[BG-C/MKT-C-MAT],[Level 4 Description1],[Level 3 Description],[Level 5 Description1], [Level 6 Description1]))/vUnit),

(sum(TOTAL<[BG],[BU],[Level 4 Description],[Business],[Business Model]>{$<[BG-C/MKT-C-MAT]={'BG-C','MKT-C'},FlagSource={'MAT Sales'},Month={"$(=Max(Month))"},MonthL=>}aggr(sum({$<[BG-C/MKT-C-MAT]={'BG-C','MKT-C'},FlagSource={'MAT Sales'},Month={"$(=Max(Month))"},MonthL=>}[LY Actual]),[BG-C/MKT-C-MAT],[Business],[Business Model],[BG],[BU],[Level 4 Description]))/vUnit)
)),'##.#%')
)))

Gabbar
Creator III
Creator III

As I can Notice there are three dimension, The total that is being shown is the Value which will correspond to dimension number.
Try aggregating the value on the finest Dimension, it should work.

Regards, Akash Dhandhi.
If this resolves your Query please like and accept this as an answer.