Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

request help for aggr function

Hi guys, i've got a little bit problem by using aggr function, see my file pls, first.

There is a simple example with 5 records with the same value on field Codice.

I need to count the last one based on Data in monitoraggio in my formula(see the formula in bar chart).

I don't know why is not working well.....

I see only one record in bar chart(correct) but it should be the last one, not the first as it's.

Pls help me.

Thx a lot.

P.S.

Near the bar chart there's the same in straight table format and i adde d calulcated dimension with my aggr function, the value of this dimension is correct, but the other dimension are related to the first record of my datas.....that's wrong for my target.

7 Replies
swuehl
MVP
MVP

Not sure if I've understood what you want to achieve.

I think that you don't want to group by Esito, but by Codice and then you need retrieve the values for the last date in the given date range.

Maybe you can use FirstSortedValue() function for that.

Create a chart with dimension Codice and then as expressions something like:

=Date(FirstSortedValue({<[Data in monitoraggio] = {">=$(=Date(date_lo))<=$(=Date(date_hi))"}>} [Data in monitoraggio],-[Data in monitoraggio]))

resp.

=FirstSortedValue({<[Data in monitoraggio] = {">=$(=Date(date_lo))<=$(=Date(date_hi))"}>} Esito, -[Data in monitoraggio])

See also attached,

Stefan


Not applicable
Author

yes,it's seem to be the right way,but.....really i need to create a bar chart with esito as main dimension, the bar chart should show one bar with one value as count, but the esito dimension should be Chiusa,indeed, in my example, it shows Complessa Power.

so, may you change your example to gain this goal ?

thx you so much

swuehl
MVP
MVP

Ok, maybe try as expression in your bar chart

COUNT( {<[Data in monitoraggio] = {">=$(=Date(date_lo))<=$(=Date(date_hi))"} >} distinct aggr( if(max(total<Codice> {<[Data in monitoraggio] = {">=$(=Date(date_lo))<=$(=Date(date_hi))"} >} [Data in monitoraggio]) =[Data in monitoraggio],Codice), Codice, Esito, [Data in monitoraggio]))

Not applicable
Author

ok,we're near to the goal.....

now, pls help me to apply your suggestions to one more complex formula :

COUNT(distinct If(flag_tipo_vista = 'A'  and az_tipo_sospensione<>'LAVORABILE'

and aggr(max([Data in monitoraggio]),Codice) <= date_hi and ([Data chiusura] > date_hi or ISNULL([Data chiusura])), Codice))

in substance, this one has two condition (flag_tipo_vista = 'A'  and az_tipo_sospensione<>'LAVORABILE') and different conditions on the date fields (Data in monitoraggio<=date_hi) and condition on Data chiusura (>=date_hi or isnull())

my formula is the rappresentation of my wrong attempt to have the last record by using this formula....

I hoper is clear..

swuehl
MVP
MVP

You should be able to add these two conditions to the set expressions:

COUNT( {<[Data in monitoraggio] = {">=$(=Date(date_lo))<=$(=Date(date_hi))"}, flag_tipo_vista = {A}, az_tipo_sospensione -= {LAVORABILE} >} distinct aggr( if(max(total<Codice> {<[Data in monitoraggio] = {">=$(=Date(date_lo))<=$(=Date(date_hi))"}, flag_tipo_vista = {A}, az_tipo_sospensione -= {LAVORABILE} >} [Data in monitoraggio]) =[Data in monitoraggio],Codice), Codice, Esito, [Data in monitoraggio]))

The expression syntax checker will probably mark this expression (starting from the minus-equal sign: -= {LAVORABILE} ) with a red underline, but this is a bug (the status should say 'Expression OK').

Not applicable
Author

ok, and what about data chiusura condition ?

the finel formula must contains this condition too : and ([Data chiusura] > date_hi or ISNULL([Data chiusura]))

thx a lot.

swuehl
MVP
MVP

I would suggest that you set a dummy date (like 31/12/9999) if this date is null in the script:

LOAD

...

if(len(trim([Data chiusura]))=0,makedate(9999,12,31),[Data chiusura]) as [Data chiusura]

...

from TABLE;

(or create an additional field if you need Data chiusura with NULLs).

Then it could look like

=

COUNT( {<[Data in monitoraggio] = {">=$(=Date(date_lo))<=$(=Date(date_hi))"}, [Data chiusura] = {">$(=Date(date_hi))"},

flag_tipo_vista = {A}, az_tipo_sospensione -= {LAVORABILE} >} distinct aggr( if(max(total<Codice> {<[Data in monitoraggio] = {">=$(=Date(date_lo))<=$(=Date(date_hi))"}, [Data chiusura] = {">$(=Date(date_hi))"}, flag_tipo_vista = {A}, az_tipo_sospensione -= {LAVORABILE} >} [Data in monitoraggio]) =[Data in monitoraggio],Codice), Codice, Esito, [Data in monitoraggio]))