Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Here you have expression for value 'A' only what about value B & C?
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')))))
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])))
It's same!Only getting values for the first column for remaining it is displaying zeros
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.
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])))
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))))
Please share sample app to look at
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