topic Re: Group data based on calculation results in QlikView

Group data based on calculation results

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

Hi All,

I have table like below.

Current Value	Target Value	(Current - Target) Delta
100	50	50
120	60	60
77	80	-3
62	22	40
70	100	-30
50	51	-1

I want to show a total of (Current - Target) Delta <0 and (Current - Target) Delta >= 0 in KPI objects. How to do that?

Thank you,

Mandi

Re: Group data based on calculation results

aarkay29 — Tue, 02 May 2017 15:13:54 GMT

Based on what you provided

may be this

Sum(Aggr(only({<[Current Value]={"=[Current Value]>[Target Value]}>}[Current Value]-[Target Value]),[Current Value],[Target Value])) for '>0'

Sum(Aggr(only({<[Current Value]={"=[Current Value]<[Target Value]"}>}[Current Value]-[Target Value]),[Current Value],F2)) for '<0'

But there should be a proper ID to aggregate instead using values

If you have a key then use this

Sum(Aggr(only({<ID={"=[Current Value]>[Target Value]"}>}[Current Value]-[Target Value]),ID))

Re: Group data based on calculation results

sunny_talwar — Tue, 02 May 2017 15:16:29 GMT

May be this

>= 0

Sum(RangeMax(([Current Value] - [Target Value]), 0))

and

< 0

Sum(RangeMin(([Current Value] - [Target Value]), 0))

Re: Group data based on calculation results

sunny_talwar — Tue, 02 May 2017 17:06:53 GMT

Sample attached

Re: Group data based on calculation results

mhmhmh91 — Wed, 03 May 2017 15:03:52 GMT

It seems the result is not correct. Thank you all the same!

Re: Group data based on calculation results

mhmhmh91 — Wed, 03 May 2017 15:04:15 GMT

It solves my problem. Thank you very much!