Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
sowmi
Creator
Creator

Total in Pivot qliksense

Hello all,

I am using pivot table based on calculations ,i have negative values ,so i need to replace negative values with zero,So i did within pivot table and showing zero for negative values ,And additionally  i need to display total so what i am facing is In TOTAL'S ,it is summing up negative values instead of "Zero",

For example :  O/p from pivot   expecting o/p

                             -1                              0

                             -2                              0

                               1                              1

                             2                                 2 

Total :                0                                 3

 Can Someone Help

Thanks                                          

Labels (2)
19 Replies
sowmi
Creator
Creator
Author

Dimension= Valuelist('A','B','C')

Measure 

=If(Valuelist('A','B','C')='A',
Sum(aggr(If(($(v5Caveragesellout)+ ($(vTargetC) - $(v5Current))*$(v5Cdailysellout))>0,($(v5Caveragesellout)+ ($(vTargetC) - $(v5Current))*$(v5Cdailysellout))),[Material Brand], Valuelist('A','B','C')))

 

It's not a direct measure ,let me know if i need to elaborate variables in measure

Kushal_Chawda

Here you have expression for value 'A' only what about value B & C?

sowmi
Creator
Creator
Author

Dimension= Valuelist('A','B','C')

Measure 

=If(Valuelist('A','B','C')='A',
Sum(aggr(If(($(v5Caveragesellout)+ ($(vTargetC) - $(v5Current))*$(v5Cdailysellout))>0,($(v5Caveragesellout)+ ($(vTargetC) - $(v5Current))*$(v5Cdailysellout))),[Material Brand], Valuelist('A','B','C')),

If(Valuelist('A','B','C')='B',
Sum(aggr(If(($(v5Baveragesellout)+ ($(vTargetb) - $(v5BCurrent))*$(v5Bdailysellout))>0,($(v5Baveragesellout)+ ($(vTargetB) - $(v5BCurrent))*$(v5Bdailysellout))),[Material Brand], Valuelist('A','B','C')),

If(Valuelist('A','B','C')='C',
Sum(aggr(If(($(v5CAaveragesellout)+ ($(vTargetCA) - $(v5CACurrent))*$(v5CAdailysellout))>0,($(v5CAaveragesellout)+ ($(vTargetCA) - $(v5CACurrent))*$(v5CAdailysellout))),[Material Brand], Valuelist('A','B','C')))))

 

Kushal_Chawda

try below

=Pick(math(Valuelist('A','B','C'),'A','B','C'),

Sum(aggr(If(($(v5Caveragesellout)+ ($(vTargetC) - $(v5Current))*$(v5Cdailysellout))>0,($(v5Caveragesellout)+ ($(vTargetC) - $(v5Current))*$(v5Cdailysellout))),[Material Brand])),

Sum(aggr(If(($(v5Baveragesellout)+ ($(vTargetb) - $(v5BCurrent))*$(v5Bdailysellout))>0,($(v5Baveragesellout)+ ($(vTargetB) - $(v5BCurrent))*$(v5Bdailysellout))),[Material Brand])),

Sum(aggr(If(($(v5CAaveragesellout)+ ($(vTargetCA) - $(v5CACurrent))*$(v5CAdailysellout))>0,($(v5CAaveragesellout)+ ($(vTargetCA) - $(v5CACurrent))*$(v5CAdailysellout))),[Material Brand])))
sowmi
Creator
Creator
Author

It's same!Only getting values for the first column for remaining it is displaying zeros

Kushal_Chawda

Can try running expression individually and check whether expression for B & C is returning any value? May be some selection causing it zero. try removing selection.

Kushal_Chawda

there was a typo in expression try below

=Pick(match(Valuelist('A','B','C'),'A','B','C'),
Sum(aggr(If(($(v5Caveragesellout)+ ($(vTargetC) - $(v5Current))*$(v5Cdailysellout))>0,($(v5Caveragesellout)+ ($(vTargetC) - $(v5Current))*$(v5Cdailysellout))),[Material Brand])),

Sum(aggr(If(($(v5Baveragesellout)+ ($(vTargetb) - $(v5BCurrent))*$(v5Bdailysellout))>0,($(v5Baveragesellout)+ ($(vTargetB) - $(v5BCurrent))*$(v5Bdailysellout))),[Material Brand])),

Sum(aggr(If(($(v5CAaveragesellout)+ ($(vTargetCA) - $(v5CACurrent))*$(v5CAdailysellout))>0,($(v5CAaveragesellout)+ ($(vTargetCA) - $(v5CACurrent))*$(v5CAdailysellout))),[Material Brand])))
sowmi
Creator
Creator
Author

Yeah i corrected that and tried individually still not getting values ,but if i use the below expression,I am getting values for all the columns but the thing is in totals it is summing all the positive and negative values but in Total need to sum only positive values.I guess aggr with valuelist is causing problem,but not sure.

=If(Valuelist('A','B','C')='A',if(
($(v5Caveragesellout)+
($(vTargetC) - $(v5CurrentC))
*
$(v5Cdailyselloutt))>0,($(v5Caveragesellout)+
($(vTargetC) - $(v5Current))
*
$(v5CHdailysellout))))

 

Kushal_Chawda

Please share sample app to look at

sowmi
Creator
Creator
Author

Instead of using Value List ,i created new dimension with value list values  as new field in back-end and in my expression used sum(aggr(if(Sum(sales)>0,sum(sales),0),dimension1,dimension2)),Then it worked .

@Kushal_Chawda  thanks for input