Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
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
Specialist
Specialist

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.

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
Specialist
Specialist

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.