5 Replies Latest reply: Jul 18, 2012 11:05 PM by Anirban Mukherjee RSS

    Removing records which had data for Last month but not this monthT

      Hi All,


      I have a dashboard which calculates YTD Values  for stores. These stores roll up to markets. I have one store "29875" which belonged to "Market A" on the month of January, but has moved to "Market B" from the month of February onwards. Since, my App is of incremental nature, the sales amount for that store for the month of January still carries on when I do the YTD (June) for Market A.


      My YTD Formula is as follows:


      num(Sum({$<YEAR={$(vMaxYear)}, MONTH_NO={"<=$(=vMaxMonth)"}, ,MONTH=>}US_AMOUNT)/(Sum({$<YEAR={$(vMaxYear)}, MONTH_NO={$(vMaxMonth)},MONTH=>}YTD_Days)),'$#,##0;($#,##0)')    


      Here vMaxYear=2012 & vMaxMonth=7                   


      This above expression calculates the YTD value for all the stores (for June) under "Market A" along with the store "29875" taking the amount of month of "January". This store moved out to "Market B " after "January".


      How can I get rid off the amount associated with the store "29875" for all the months after January for "Market A' so that YTD(Feb) or YTD(June) etc doesn't include the amount associated with this store anymore. The YTD values of store "29875" should only account for "Market B" from February onwards which it does.


      Bottomline: I dont want the Market A to carry over the YTD values when the store doesn't belong to him on that month.


      All suggestions will be highly appreciated.