How to calculated MTD,YTD,QTD values in HANA information models

[Former Member]

Hi all,

I am new to HANA and trying to build a balance sheet report which has amounts displayed in MTD,QTD and YTD totals for current and previous years.I have created attribute views for G\L account and profit center and an analytical view with FAGLFLEXA table and now want to build a calculation view to get the report output.Can anyone please help me calculated the MTD,QTD and YTD values.

Thanks,

Sunitha

[justin_molenaur2]

Hi Sunitha, I am just wondering if you managed to come up with a solution on this? I am trying to come up with an approach either from a modeling perspective or from a reporting perspective.

In a modelling perspective, I would think it would be viable to create restricted measures that use dynamic expressions and capture each of the required time periods (YTD/PYTD, QTD/PYQTD, MTD/PYMTD).

This seems pretty straightforward, until you get a situation where there are many many measures that will need this treatment. If we consider COPA, there could be over 100 measures involved on the CE1* table, so I think this approach would not work as it would result in 6x the measures (YTD, PYTD, MTD, PYMTD, QYD, PYQTD).

Does anyone else have any insight as to how they solved?

Thanks,

Justin

[former_member184768]

Hi Justin,

We implemented Projections on the Calc view based on COPA Tables / Analytic views. These projections are defined with filter ranges on Fiscal Period to derive MTD, QTD, YTD values for Current Year, Prev Year and Prev to Prev Year. The projections are defined with Constant columns for Query pruning.

The projections are then again put in a UNION for the final reporting.

Regards,

Ravi

[yeushengteo]

Hi Justin,

Try following SQL syntax statement on your view and see if you can get some useful result.

SELECT Year("BillingDocumentDate_E") as "Invoice_Year",

               "BillingDocumentDate_E" as "Invoice_Date",

              round("TotalNetAmount_E") as "Billed_Amount",

              round(Sum("TotalNetAmount_E") OVER (partition by Year("BillingDocumentDate_E") order by "BillingDocumentDate_E")) as "YTD_Amount",

              round(Sum("TotalNetAmount_E") OVER (partition by Quarter("BillingDocumentDate_E") order by "BillingDocumentDate_E")) as "QTD_Amount",

            round(Sum("TotalNetAmount_E") OVER (partition by Month("BillingDocumentDate_E") order by "BillingDocumentDate_E")) as "MTD_Amount"

FROM <view>

WHERE <abc>

GROUP BY Year("BillingDocumentDate_E"), "BillingDocumentDate_E", "TotalNetAmount_E"

ORDER BY 1, 2

Regards.

YS

[justin_molenaur2]

Hi Ravi, thanks the for insight. I am thinking of a similar approach but I am having trouble visualizing the method you are suggesting.

- How many total projections are involved?

- Where/how are you defining the PTD/QTD/YTD? Are these restricted measures in each projection using an input parameter to drive (dynamic)?

- How many measures did you implement in this way, because of course it will multiply the total number of measures, whereas COPA typically has over 100 base measures.

- With regards to the constant column approach, this is a design time option on the Union, what columns are you using for a constant mapping? Is it year?

The other option I was thinking is to use the base Analytic view and use some date logic from the presentation layer, which may work too.

Thanks,

Justin

[former_member184768]

Hi Justin,

Please find the blog on this topic here. There can be additional approaches for the same, which should be available for discussion soon.

Regards,

Ravi

[Former Member]

Is it FAGLFLEXT (Totals)?