Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

reporte_gestao1
New Contributor

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

Re: pivot measure divide current sum for sales sum

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

3 Replies
jwjackso
Contributor III

Re: pivot measure divide current sum for sales sum

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%'))

Re: pivot measure divide current sum for sales sum

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

reporte_gestao1
New Contributor

Re: pivot measure divide current sum for sales sum

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

Community Browser