Solved: Re: Aggr function not working - Qlik Community

tracycrown · ‎2016-05-11

Dear all

I managed to compute the average score 53.0 for 5 batches but failed to show the result, please advise urgently.

Thank you so much

Tracy

sunny_talwar · ‎2016-05-15

Here you go, try these expressions:

With Type:

If(Dimensionality() <= 1, Aggr(If(Avg(TOTAL Score) >= [Lower] and avg(TOTAL Score)<= [Upper], Result), Batch, Type, Result), Aggr(If(avg(Score)>= [Lower] and avg(Score)<= [Upper], Result), Batch, Type, Result))

With Type and Item

If(Dimensionality() <= 2, Aggr(If(avg(TOTAL Score)>= [Lower] and avg(TOTAL Score)<= [Upper], Result), Batch, Type, Item, Result), Aggr(If(avg(Score)>= [Lower] and avg(Score)<= [Upper], Result), Batch, Type, Item, Result))

View solution in original post

sunny_talwar · ‎2016-05-11

What are you expecting to see?

engishfaque · ‎2016-05-12

Dear Tracy,

Kindly find attached App, I hope you are looking for that one.

For quick output, screenshot is given below:

Kind regards,

Ishfaque Ahmed

sunny_talwar · ‎2016-05-12

Ishfaque‌ - I think using MinString is not going to work. For instance if I select Batch 10004 and 1005, I should be seeing Bad, but because minstring of Bad and Average is Average, I still see Average

tracycrown · ‎2016-05-12

Dear Ishfaque Ahmed

Kindly advise what is the function (or purpose) of MinString in the script.

Thank you very much Tracy

sunny_talwar · ‎2016-05-12

MinString works similar to Min function, but the unique thing is that it works on Text field. So since Average begins with an A, it is smaller than Bad which begins with B. Does that make sense?

tracycrown · ‎2016-05-12

Dear Sunny

So what is your answer for this, change MinString to AvgString ?.

Thank you

Tracy

sunny_talwar · ‎2016-05-12

I don't even think AvgString exists, but this is not the right approach. Do you see the screenshot above? Is Average the right result. I don't think so, but you would know better. We def. need to look for another approach here.

sunny_talwar · ‎2016-05-12

Tract try this expression:

If(Dimensionality() = 0, Aggr(If(avg(TOTAL Score)>= [Lower] and avg(TOTAL Score)<= [Upper], Result), Batch,Result), Aggr(If(avg(Score)>= [Lower] and avg(Score)<= [Upper], Result), Batch,Result))

With no selections

With Selection of Batch 10004 and 10005

tracycrown · ‎2016-05-15

Dear Sunny

Thank you so much for your help, your advice works well in a simple dataset.

However, it does not work if more fields (Type, Item) are added, please help. Sorry to say that Qlikview do not allow me to attach Test-2.xlsx.

Batch	Type	Item	Score
10001	A	A101	70
10001	A	A102	80
10002	B	B101	67
10002	A	A102	27
10003	C	C101	56
10003	B	B101	35
10004	D	D101	42
10004	E	E101	21
10005	A	A102	30
10005	B	B102	19

Thank you

Tracy