Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
jduluc12
Contributor

Aggr to calculate perc

Hi,

My data looks like this

CustomerPriceCost
Cust 11000
Cust 24010
Cust 37525
   

       

The chart should look like this

CustomerPriceCostperc
Cust 11000 
Cust 24010((40-10) / 40 ) * 100 =  75%
Cust 37525((75-25) / 75) * 100 =  66%
 21535((40+75)-(10+25)) / (40+75) ) * 100 =  69%

 

My expression looks like this

sum(aggr(if (Cost > 0, (sum(Price)- sum(Cost))/sum(Price)) ,Cust))

But this brings up 75% + 66% = 141%

I am using "Show percanatge option" in Numbers tab to show percantage.

 

If I remove sum from in front of expression the Total row comes out as blank.

what am I doing wrong here?

Please help.

Jean

Labels (1)
1 Solution

Accepted Solutions
jduluc12
Contributor

Re: Aggr to calculate perc

Hi Anushree,

Thanks for replying.

I tried that but It did not work.

I did something like this to make it work.

(sum(aggr(if (Cost>0,sum(Price)),Cust))

- sum(aggr(if (Cost>0,sum(Cost)),Cust)))

/sum(aggr(if (Cost>0,sum(Price)),Cust))

Jean

View solution in original post

2 Replies
anushree1
Valued Contributor II

Re: Aggr to calculate perc

I believe you are using a straight table with Cust Dimension , so no need to use aggr, below expression must suffice:

if (Cost > 0, (sum(Price)- sum(Cost))/sum(Price))

Please check and let me know if that does not work

jduluc12
Contributor

Re: Aggr to calculate perc

Hi Anushree,

Thanks for replying.

I tried that but It did not work.

I did something like this to make it work.

(sum(aggr(if (Cost>0,sum(Price)),Cust))

- sum(aggr(if (Cost>0,sum(Cost)),Cust)))

/sum(aggr(if (Cost>0,sum(Price)),Cust))

Jean

View solution in original post