16 Replies Latest reply: Oct 8, 2015 9:14 AM by Mohamed Ahid RSS

    How to see 12 months back in a chart with Max(date)

    Darri Hilmarsson

      Hi, QV

       

      I have a dataset that looks like that

       

       

      DateUnitIDActivity Type
      1.12.201541
      1.12.201551
      1.12.201561
      1.12.201572
      2.10.201582
      2.10.201591
      2.10.2015102
      2.10.2015111

       

      I am want to know how many UnitID have activity type = 1

      So I have used this formula

      =count({$< [Activity Type] = {'1'} >}UnitID)/count(UnitID)

       

      Then I wanted to see 12 month back. So I used this formula:

      =count({$< [Activity Type] = {'1'}, Date={'>=$(=MonthStart(Today(), -11))<=$(=Today())'}  >}UnitID)/count(UnitID)

       

      But the problem with this is when I click on date in 2014 I get no data to display which is not strange.

       

      But I how can I see ALWAYS 12 months back. So when I select date in 2014 that is my max(date) and see 12 months back from that date.

       

      I was wondering how can I use Max(date) in the formula?