1 Reply Latest reply: Apr 8, 2014 2:41 AM by Ravi Kumar RSS

    comparing last year with this years latest month

      I got these two expressions

      count({<year={'2013'},Stationsname ={'1','2','3'},Gruppe={'washers'} >}items)

      count({<year={'2014'},Stationsname ={'1','2','3'},Gruppe={'washers'} >}items)

       

      and then I filter on two fields month and dates to get the same month and same range of dates ex. 1. april to 8. april so I can

      compare the two years and same periode month and days

       

      I would like to avoid the filtering and incorporate somthing like max(month)) in the two expressions

      something like this - but what about last year max(MONTH) year=2013 will give me december????????

      count({<year={'2014'},MONTH={Max(MONTH)},Stationsname ={'1','2','3'},Gruppe={'washers'} >}items)

       

        • Re: comparing last year with this years latest month
          Ravi Kumar

          Try like this

           

          MaxMonth_CurYear = If(Year=Max(Year),Date(Max(DateField),'MMM')) or

                                        = If(Year=Max(Year),Date(Max(DateField),'MM'))

                                   //Dependson your Month field format (MMM/MM)

           

          MaxMonth_PrevYear = If(Year=Max(Year)-1,Date(AddMonths(Date(Max(DateField),'DD/MM/YYYY'),-4),'MMM') or

                                         = If(Year=Max(Year)-1,Date(AddMonths(Date(Max(DateField),'DD/MM/YYYY'),-4),'MMM')

                                  //Dependson your Month field format (MMM/MM)

           

          Ex: MaxMonth as per the maxdate is Apr then Date(AddMonths(Date(Max(DateField),'DD/MM/YYYY'),-4) will return Dec

           

          After creating these variables and you can use these in set analysis