Issue with Aggr function

[Anonymous]

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.

[trdandamudi]

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

                   ID

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

Hope this helps...

[sunny_talwar · Accepted solution]

May be this

Dimension

DATE

Expression

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

[Anonymous]

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

[Anonymous]

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.

[tresesco]

May be in both the aggregation functions, like:

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

[Anonymous]

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.

[sunny_talwar]

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

[Anonymous]

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

[sunny_talwar]

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