topic Re: Chart Expression in QlikView

Chart Expression

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

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,

Re: Chart Expression

sunny_talwar — Mon, 11 Sep 2017 12:13:03 GMT

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

Re: Chart Expression

dunnalahk123 — Mon, 11 Sep 2017 16:04:01 GMT

Thank You so much Sunny it works 🙂

Re: Chart Expression

dunnalahk123 — Mon, 09 Oct 2017 07:06:37 GMT

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.

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,

Re: Chart Expression

dunnalahk123 — Mon, 09 Oct 2017 08:01:41 GMT

Hi Sunny,

I found another way. Thank You.

Best Regards,