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: 
Anonymous
Not applicable

pivot measure divide current sum for sales sum

Hello, i'm trying to calculate the sum values over the sum of sales. So far i managed to divide it properly but only on totals, som how it doesn't assume the collumn filter. It must be missing something in my formula.

if(isnull(Sum(CBL_ValorT)) or Sum(CBL_ValorT)=0, 0, num(fabs(Sum(CBL_ValorT))/Sum({<Group1={"0.*"}>}CBL_ValorT),  '#.##0,0%'))

it's returning this,

shopdetail.jpg

but it should return this.

shopgoal.jpg

i know this is because is dividing over the total and not over the column, but i can't get to it.

totals.jpg

thks in advance.

1 Solution

Accepted Solutions
sunny_talwar

Or you might need to add your first dimension within the total qualifier

If(IsNull(Sum(CBL_ValorT)) or Sum(CBL_ValorT) = 0, 0, Num(fabs(Sum(CBL_ValorT))/Sum({<Group1={"0.*"}>}TOTAL <1stDimensionHere> CBL_ValorT),  '#.##0,0%'))

Replace the 1stDimensionHere with the field name which contain RG because this will ensure that you divide by the 0. value of that particular 1st dimension....

View solution in original post

3 Replies
jwjackso
Specialist III
Specialist III

See if adding Total calculates the number you need:

if(isnull(Sum(CBL_ValorT)) or Sum(CBL_ValorT)=0, 0, num(fabs(Sum(CBL_ValorT))/Sum({<Group1={"0.*"}>}Total CBL_ValorT),  '#.##0,0%'))

sunny_talwar

Or you might need to add your first dimension within the total qualifier

If(IsNull(Sum(CBL_ValorT)) or Sum(CBL_ValorT) = 0, 0, Num(fabs(Sum(CBL_ValorT))/Sum({<Group1={"0.*"}>}TOTAL <1stDimensionHere> CBL_ValorT),  '#.##0,0%'))

Replace the 1stDimensionHere with the field name which contain RG because this will ensure that you divide by the 0. value of that particular 1st dimension....

Anonymous
Not applicable
Author

Thank you, that really solve it. Now it presents the weight over the each shop sales.