Monthly statement (Table)

[#1]

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

[rahulpawarb]

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