topic Re: Aggr function not working in QlikView

Aggr function not working

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

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

Re: Aggr function not working

sunny_talwar — Thu, 12 May 2016 01:37:15 GMT

What are you expecting to see?

Re: Aggr function not working

engishfaque — Thu, 12 May 2016 05:18:38 GMT

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

Re: Aggr function not working

sunny_talwar — Thu, 12 May 2016 08:16:56 GMT

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

Re: Aggr function not working

tracycrown — Thu, 12 May 2016 08:24:52 GMT

Dear Ishfaque Ahmed

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

Thank you very much Tracy

Re: Aggr function not working

sunny_talwar — Thu, 12 May 2016 08:27:13 GMT

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?

Re: Aggr function not working

tracycrown — Thu, 12 May 2016 08:30:49 GMT

Dear Sunny

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

Thank you

Tracy

Re: Aggr function not working

sunny_talwar — Thu, 12 May 2016 08:33:28 GMT

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.

Re: Aggr function not working

sunny_talwar — Thu, 12 May 2016 10:02:56 GMT

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

Re: Aggr function not working

tracycrown — Mon, 16 May 2016 00:52:55 GMT

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

Re: Aggr function not working

sunny_talwar — Mon, 16 May 2016 01:02:25 GMT

Did you try adding the new dimensions to your Aggr() function when you added them to the dimension?

When only Type is added, try this:

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

When Type and Item are added

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

When you add a dimension to the chart, you will most likely need to add it to the Aggr() expression also. Check out the attached

Re: Aggr function not working

sunny_talwar — Mon, 16 May 2016 01:03:03 GMT

To attach a sample, check out the following link:

Uploading a Sample

Re: Aggr function not working

tracycrown — Mon, 16 May 2016 02:02:48 GMT

Dear Sunny

Yes, I did add Type and Item in the Aggr() function, please refer to attached files for details.

Thank you, Tracy

Re: Aggr function not working

sunny_talwar — Mon, 16 May 2016 02:10:19 GMT

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

Re: Aggr function not working

tracycrown — Mon, 16 May 2016 06:25:31 GMT

Dear Sunny

Thank you for your help, please advise what is the meaning of <= 1 or 2.

Tracy