3 Replies Latest reply: Jun 2, 2017 10:11 AM by Sunny Talwar RSS

    Last Month Where Data and Date/Text Issue

    Ben Roberts

      Dear all,

       

      I have been battling with this problem for a few days and I suspect there is a simple solution.

       

      I have a list of account details with revenue by month but the month data goes to end of 2017 and with 0 where in the future. The current month of data is APR-17 and would not always be the current month or current month-1. I ultimately want to get to a solution where I can do a sum of the revenue for current month versus last month so I can sort it to show largest changes month on month.

       

      I have been trying to create month on month analysis by isolating the current max month where there is data (see variable below) and using the variable as the input for the set analysis (see formula).

       

      I have tried many variations but cannot get the set analysis to accept the variable result. I think it has something to do with the format of the cell but even in varying the solutions around this I get no result, it will only accept if I hard code "APR-2017" as the value.

       

      Variable (MaxMonth)

      =AGGR(max({<FACT_MONTHYEAR_DATE={"=sum(AMOUNT)>0"}>} date#(FACT_MONTHYEAR_DATE,'MMM-YYYY')),CLIENT_KEY)

       

      Formula

      AGGR(SUM({$<FACT_MONTHYEAR_DATE={$(=MaxMonth)}>}AMOUNT),FACT_MONTHYEAR_DATE)

       

      I have attached my excel source data and a QVD I have been working on. Thanks in advance for any suggestions.

       

      Ben