2 Replies Latest reply: Nov 2, 2016 12:26 PM by Pedro Lopes RSS

    Compare the preview month with the current month on same day

    Pedro Lopes

      Hi guys,


      Anyone know how i built a expression to compare the preview month with the current month on same day?

       

      Exemple:

       

      Today is 24 of October and i have sales from 1 to 24 October 200.000$

       

      I want compare this sales to last month on the same day.

       

      24 of September vs 24 October.

       

      Thanks in advance,

      Pedro Lopes

        • Re: Compare the preview month with the current month on same day
          Sunny Talwar

          Try reading about The As-Of Table here and see if this helps

            • Re: Compare the preview month with the current month on same day
              Pedro Lopes

              Thanks Sunny for this link.

               

              How can apply this method on my case?

               

              Concretely i want create 3 measures on my dashboard:


              1 -  Compare this year vs last year until now ex: [01-jan-2015 to 02-nov-2015] vs [01-jan-2015 to 02-nov-2015]

              ex: YTD vs YTD (year-1) ??

               

               

              2 - Compare this month vs last month until now ex:[01-oct-2016 to 02-oct-2016] vs [01-nov-2016 to 02-nov-2016]

              ex: MTD vs MTD (month-1)





              3 - Compare last day loaded (yesterday) vs (yesterday-1) ex: [31-oct-2016] vs [01-nov-2016]

              ex:

              - yesterday ---- sum({1<date_id={">=$(=Max(date_id))"}>}N_of_downloads ) ...works.

              -yesterday  ---- sum({1<date_id={">=$(=Max(date_id)-1)"}>}N_of_downloads ) ...didn't work.






              My master calendar is on this format:

              Dim_Date:

              LOAD

                  date(F1,'YYYYMMDD') as date_id,

                  "Day",

                  "Week",

                  Week_day,

                  "Month",

                  "Year",

                  MonthYear

              FROM [.....]

              (qvd);

              where date_id >= $(vMinDate) and date_id <= $(vMaxDate);

               


              Thanks in advance.