topic Re: Issue with Aggr function in QlikView

Issue with Aggr function

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

Hi everybody,

i have this example:

ID	DOC	STATE	DATE
123	1	OPEN	01/04/2016
123	2	CLOSE	30/03/2016
123	3	PROGS	15/03/2016
124	1	OPEN	03/04/2016
124	2	CLOSE	15/05/2016

I need to create a bar char that counts:

To ID=123 ---> bar char shows 1 in 01/04/2016 (in max date)

To ID=124 ---> bar char shows 1 in 15/05/2016 (in max date)

I dont know if use an aggr function to group by ID, and use max(Date) to show data.

Besides, in another char, i have to group by state (the state of last date).

I only use char Expressions,

Can anyone help me?

Thanks a lot!!

Regards,

Paula.

Re: Issue with Aggr function

trdandamudi — Tue, 02 May 2017 14:16:48 GMT

For your first point I think the below should work:

Dimension: ID

Expression: Sum({<DATE={"=Max(DATE)"}>}DOC)

For the second point (STATE):

Dimension: STATE

Expression: Sum({<DATE={"=Max(DATE)"}>}DOC)

Hope this helps...

Re: Issue with Aggr function

sunny_talwar — Tue, 02 May 2017 14:35:00 GMT

May be this

Dimension

DATE

Expression

=Sum(Aggr(If(DATE = Max(TOTAL <ID> DATE), 1, 0), ID, DATE))

Re: Issue with Aggr function

Anonymous — Wed, 03 May 2017 05:58:57 GMT

Thanks a lot for your response Thirumala.

I need to show this data using DATE as dimension:

I want to show in 01/04/2017 --> count ID (and it choose 123 counting 1)

I think i have to use aggr function to group by ID choosing the most current DATE but i don know how...

Re: Issue with Aggr function

Anonymous — Wed, 03 May 2017 06:47:02 GMT

Thank you Sunny!!

It seems works properly, but if y select only January (After having created the char with your expression), the Total changes... and i dont know why

Why are you using SUM instead of COUNT?

And if i want to group by State taking into account the previous especification,

Where I should add the set analysis in this expression? For Example to "OPEN"

Dimension

DATE

Expression

=Sum(Aggr(If(DATE = Max(TOTAL <ID> DATE), 1, 0), ID, DATE))

Thank you Sunny!

Regards,

Paula.

Re: Issue with Aggr function

tresB — Wed, 03 May 2017 06:56:22 GMT

May be in both the aggregation functions, like:

Sum( {<STATE={'OPEN'}>} Aggr(If(DATE = Max( {<STATE={'OPEN'}>} TOTAL <ID> DATE), 1, 0), ID, DATE))

Re: Issue with Aggr function

Anonymous — Wed, 03 May 2017 11:47:55 GMT

Thank you Tresesco, but i think i only have to use set analysis in the first part:

Sum( {<STATE={'OPEN'}>} Aggr(If(DATE = Max( TOTAL <ID> DATE), 1, 0), ID, DATE))

To sum only that ids which have this state.

Re: Issue with Aggr function

sunny_talwar — Wed, 03 May 2017 11:56:32 GMT

You don't want the expression to change based on selection in Month field? May be this

=Sum({<Month>}Aggr(If(Only({<Month>} DATE) = Max({<Month>}TOTAL <ID> DATE), 1, 0), ID, DATE))

Re: Issue with Aggr function

Anonymous — Wed, 03 May 2017 12:43:58 GMT

Sorry Sunny,

I think i have not explained well, in english is more difficult!

I have this chart with your first expresssion: Sum(Aggr(If(DATE = Max(TOTAL <ID> DATE), 1, 0), ID, DATE))

and when i select January (ene 2017), i have this:

as you can see they are different results, and i want always the same!

I have resolve this with : sum({<DATE>}Aggr(If(DATE = Max({<DATE>}TOTAL <ID> DATE), 1, 0), ID, DATE))

Re: Issue with Aggr function

sunny_talwar — Wed, 03 May 2017 12:47:39 GMT

Not completely sure, but try this

=Sum({<Month>}Aggr(If(Only({<Month>} DATE) = Max({<Month>}TOTAL <ID> DATE), 1, 0), ID, DATE)) * Avg(1)

Here replace Month with the field you are making selection in.... or try this

=Sum(Aggr(If(Only({<Month>} DATE) = Max({<Month>}TOTAL <ID> DATE), 1, 0), ID, DATE))

Re: Issue with Aggr function

Anonymous — Wed, 03 May 2017 12:55:36 GMT

Thank you Sunny,

i have resolve that problem with:

=sum({<DATE>} Aggr(If(DATE = Max({<DATE>}TOTAL <ID> DATE), 1, 0), ID, DATE))

and for STATE, i think is the same adding the STATE you want... Right??

=sum({<DATE=, STATE={'OPEN'}>} Aggr(If(DATE = Max({<DATE=, STATE={'OPEN'}>}TOTAL <ID> DATE), 1, 0), ID, DATE))

Thank you!

Re: Issue with Aggr function

sunny_talwar — Wed, 03 May 2017 13:06:24 GMT

I would guess so... try it out and see if it works or not