topic Re: Total of Average Count in Straight Table in QlikView

Total of Average Count in Straight Table

pinkzfairy — Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

I have straight table with Expression :

EA New = AVG(Aggr(Count({[Item New] <[Fact Flag]={'Sales'}>} DISTINCT [Customer SO Group Area]), [Month Name], [Customer Area SI]))

RO Target = Count({[Item Target] <[Fact Flag]={'Sales'}>} DISTINCT [Customer SO Group Area])

But i'm getting different Total [EA New]/[RO Target]. How can i get Total [EA New]/[RO Target] like in "Yellow"?

And also how can i set background colour of [EA New]/[RO Target] to "Red" if [EA New]/[RO Target] < TOTAL [EA New]/[RO Target]?

I also attach my qvw.

Thanks is advance for any help you can provide

sunny_talwar — Wed, 14 Sep 2016 16:08:21 GMT

How are you getting 57.75%? Avg of the cells in red? Why including the Null cell (D3)? I got 35.40%, does that look right?

pinkzfairy — Thu, 15 Sep 2016 01:03:25 GMT

Thanks a lot Sunny for your help

With a little modification expression you gave, i can get result like i want

sunny_talwar — Thu, 15 Sep 2016 09:59:51 GMT

Awesome, I am glad you were able to make this work. Would you be able to point out what did you do differently to get the right output?

pinkzfairy — Thu, 15 Sep 2016 14:45:23 GMT

Hi Sunny, i hope i can explain well.

This is your expression [EA New]/[RO Target] :

And this is my expression after modification [EA New]/[RO Target] :

I'm using your expression in yellow and the green. And for your expression in pink i change into Count Total without Sum Total Aggr.

And for Red background color this is my expression :

Thank you

sunny_talwar — Thu, 15 Sep 2016 23:20:34 GMT

Super, thanks for sharing this. I am glad that you played around with the expression to fine tune it based on your requirement.

Best,

Sunny