1 Reply Latest reply: Apr 27, 2017 12:10 AM by Rahul Pawar RSS

    Monthly statement (Table)

    Philipp Wicke

      Hi,

      I have been struggling to build a table (P&L) with twelve month overwiew (see Screen from QlikView) for Budget or Actual.

      One simple Excel spread as a Import file. (see attached file only with Budgettestvalues).

       

      Any suggestions on how I can get this table?

       

      Thanks!

       

      Best Regards,

      Philipp

        • Re: Monthly statement (Table)
          Rahul Pawar

          Hello Philipp,

           

          Trust that you are doing well!

           

          As a work around, you can add following measures post addition of required dimensions to the chart object.

           

          //Jan
          Sum({<Year=, Month={"<=$(=Num(Month(Max(DateKey))))"}, DateKey={">=$(=Num(AddMonths(YearStart(Max(DateKey)),0))) <=$(=Num(MonthEnd(AddMonths(YearStart(Max(DateKey)),0))))"}>}Sales)
          //Feb
          Sum({<Year=, Month={"<=$(=Num(Month(Max(DateKey))))"}, DateKey={">=$(=Num(AddMonths(YearStart(Max(DateKey)),1))) <=$(=Num(MonthEnd(AddMonths(YearStart(Max(DateKey)),1))))"}>}Sales)
          //Mar
          Sum({<Year=, Month={"<=$(=Num(Month(Max(DateKey))))"}, DateKey={">=$(=Num(AddMonths(YearStart(Max(DateKey)),2))) <=$(=Num(MonthEnd(AddMonths(YearStart(Max(DateKey)),2))))"}>}Sales)
          //Apr
          Sum({<Year=, Month={"<=$(=Num(Month(Max(DateKey))))"}, DateKey={">=$(=Num(AddMonths(YearStart(Max(DateKey)),3))) <=$(=Num(MonthEnd(AddMonths(YearStart(Max(DateKey)),3))))"}>}Sales)
          //May
          Sum({<Year=, Month={"<=$(=Num(Month(Max(DateKey))))"}, DateKey={">=$(=Num(AddMonths(YearStart(Max(DateKey)),4))) <=$(=Num(MonthEnd(AddMonths(YearStart(Max(DateKey)),4))))"}>}Sales)
          //Jun
          Sum({<Year=, Month={"<=$(=Num(Month(Max(DateKey))))"}, DateKey={">=$(=Num(AddMonths(YearStart(Max(DateKey)),5))) <=$(=Num(MonthEnd(AddMonths(YearStart(Max(DateKey)),5))))"}>}Sales)
          //Jul
          Sum({<Year=, Month={"<=$(=Num(Month(Max(DateKey))))"}, DateKey={">=$(=Num(AddMonths(YearStart(Max(DateKey)),6))) <=$(=Num(MonthEnd(AddMonths(YearStart(Max(DateKey)),6))))"}>}Sales)
          //Aug
          Sum({<Year=, Month={"<=$(=Num(Month(Max(DateKey))))"}, DateKey={">=$(=Num(AddMonths(YearStart(Max(DateKey)),7))) <=$(=Num(MonthEnd(AddMonths(YearStart(Max(DateKey)),7))))"}>}Sales)
          //Sep
          Sum({<Year=, Month={"<=$(=Num(Month(Max(DateKey))))"}, DateKey={">=$(=Num(AddMonths(YearStart(Max(DateKey)),8))) <=$(=Num(MonthEnd(AddMonths(YearStart(Max(DateKey)),8))))"}>}Sales)
          //Oct
          Sum({<Year=, Month={"<=$(=Num(Month(Max(DateKey))))"}, DateKey={">=$(=Num(AddMonths(YearStart(Max(DateKey)),9))) <=$(=Num(MonthEnd(AddMonths(YearStart(Max(DateKey)),9))))"}>}Sales)
          //Nov
          Sum({<Year=, Month={"<=$(=Num(Month(Max(DateKey))))"}, DateKey={">=$(=Num(AddMonths(YearStart(Max(DateKey)),10))) <=$(=Num(MonthEnd(AddMonths(YearStart(Max(DateKey)),10))))"}>}Sales)
          //Dec
          Sum({<Year=, Month={"<=$(=Num(Month(Max(DateKey))))"}, DateKey={">=$(=Num(AddMonths(YearStart(Max(DateKey)),11))) <=$(=Num(MonthEnd(AddMonths(YearStart(Max(DateKey)),11))))"}>}Sales)
          

           

          P.S.: Very important to have the DateKey field in number format by using Floor(Date) AS DateKey. This will gives you numeric equivalent of the respective date. Post that you can derive the required expressions.


          Reference: Date Level Analysis - WTD, MTD, QTD & YTD (Current Year & Previous Year)

           

          Regards!

          Rahul