Chart Expression

[dunnalahk123]

Hi,

I am using Chart box in Qlikview and i am using below expression

=((Sum(SSHHours)+Sum(RCSHHours)+SUM(AGGR([Total Service Credit Balance in Hours],[Project ID]))) * Avg([FTE Multiplier]))

Everything works well.

only one problem is Avg([FTE Multiplier])).

For FTE Multiplier i have one static table and it is at country level.

So when write above expression what happens is In Chat lowest level works correct but if i collapse to sub region or region or word wide . sum of values is not coming correct.

How its takes is

For Example AMS : First country =40 Hours and FTE Multiplier for that country is 0.26874

                                 So  40 * 0.26874=10.74

                                Second Country 30 hours an FTE Multiplier For that country is 0.3456

                                 So  30 * 0.3456 =10.36

So when i collapse to region it should give me 10.74 +10.36= 21.1 -----I want logic to work in this way when i collapse.

But it how its takes in qlikview is when i collapse to region 40+30=70 and Avg (0.26874 and 0.3456).

70* (Avg (0.26874 and 0.3456)). which not matches with 21.1 -------but based on Current expression it works like this which is wrong.

Can some one help me here please.

attaching is the QVW File for your reference.

Best Regards,

HK

[sunny_talwar · Accepted solution]

May be try this

=Sum(Aggr(((SUM([ASM_SSH_Hours])+SUM([ASM_RCSH_Hours])+ (SUM(AGGR([Total Service Credit Balance in Hours],[Project ID]))*0.35)) * (Avg([FTE Multiplier]))), Global, Region, Subregion, Subregion1, PSA_POCOUNTRY))

[dunnalahk123]

Thank You so much Sunny it works 🙂

[dunnalahk123]

Hi Sunny,

As per your suggestion earlier it works but summing is working correctly only on vertically as per below   if I do sum on horizontal again it not matches.

=Sum(Aggr(((SUM([ASM_SSH_Hours])+SUM([ASM_RCSH_Hours])+ (SUM(AGGR([Total Service Credit Balance in Hours],[Project ID]))*0.35)) * (Avg([FTE Multiplier]))), Global, Region, Subregion, Subregion1, PSA_POCOUNTRY))

Example:

Region  ASMFTE  TAMFTE     TotalFTE

WW                500           400         it should give me 900 but it's not working.

Region  ASMFTE  TAMFTE     TotalFTE

AMS             500           400         it should give me 900 but it's not working.

APJ              300            200        it should give me 500 but its not working

EMEA           100           100         it should give me 200 but its not working

it works at vertical level(column level)

Region  ASMFTE  TAMFTE     TotalFTE

AMS             500           400         it should give me 900 but it's not working.

APJ              300            200        it should give me 500 but its not working

EMEA           100           100         it should give me 200 but its not working

Total            900             700        vertical its working correctly-----------no problem at vertical sum as per your old suggestion suggested above, ! !

Please advise.

Best Regards,

HK

[dunnalahk123]

Hi Sunny,

I found another way. Thank You.

Best Regards,

HK