7 Replies Latest reply: Jul 11, 2012 10:15 AM by Stefan Wühl RSS

    request help for aggr function

    Alessandro Bettica

      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.

        • Re: request help for aggr function
          Stefan Wühl

          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

           

           


            • Re: request help for aggr function
              Alessandro Bettica

              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

                • Re: request help for aggr function
                  Stefan Wühl

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

                    • Re: request help for aggr function
                      Alessandro Bettica

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

                        • Re: request help for aggr function
                          Stefan Wühl

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

                            • Re: request help for aggr function
                              Alessandro Bettica

                              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.

                                • Re: request help for aggr function
                                  Stefan Wühl

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