Accumulated Values

[markusblaut]

Dear fellows,

I would like to calculate a KPI (growth). Example: I would like to sum up the pieces for 2017 as far as pieces for 2018 exist and compare them.

So in Fiscal Year 2018 there are pieces from accounting month 201710 until accounting month 201807 (yellow),

so I would like to compare them with pieces from Fiscal Year 2017 from accounting month 201610 until 201707 (green).

The yellow numbers should be Sum({<FiscalYear = {2018}>}  [Pieces])

but I don't get the green formular.

Any idea how I could manage that with a formular?

Many thanks for any support!! Best, Markus

[YoussefBelloum]

Hi,

for green cells, why don't you use this:

Sum({<FiscalYear = {2017}>}  [Pieces])

[markusblaut]

Hi Youssef,

thank you for answering!

if I would use "Sum({<FiscalYear = {2017}>}  [Pieces])" I would also add the pieces of accounting Month 201708 and 201709.

But I only want to sum up the month until 201707, to compare them with the numbers of Fiscal Year 18 (201710-201807)

Thank you!

[OmarBenSalem]

Can u share a little excel sheet or an inline table so that we can work on it?

This can be easily achieved through set analysis but all depends on the fields u have.

Please share source.

[OmarBenSalem]

And there is a thing I can't understand, how is the year 2017 associated to Months 201610,201611 and 201612?

[OmarBenSalem]

Anyway, Here's my solution:

1) add a date field:

fiscal:

load * , MonthEnd(date#(AccountingMonth,'YYYYMM')) as AccountingDate Inline [

Pieces, FiscalYear,AccountingMonth

5,2017,201610

20,2017,201611

30,2017,201612

40,2017,201701

10,2017,201702

22,2017,201703

53,2017,201704

40,2017,201705

5,2017,201706

0,2017,201707

4,2017,201708

6,2017,201709

5,  2018,201710

2,2018,201711

16,2018,201712

50,2018,201801

60,2018,201802

50,2018,201803

10,2018,201804

30,2018,201805

10,2018,201806

20,2018,201807

];

2) Use this:

Yellow:

sum({<AccountingDate,AccountingMonth,FiscalYear, FiscalYear={"$(=max(FiscalYear))"}, AccountingDate={"<=$(=max(AccountingDate))"}>}Pieces)

Green:

sum({<AccountingDate,AccountingMonth,FiscalYear, FiscalYear={"$(=max(FiscalYear)-1)"}, AccountingDate={"<=$(=AddYears(max(AccountingDate),-1))"}>}Pieces)

Result:

[markusblaut]

Thank you so much, it finally works!

[markusblaut]

In our company the fiscal year always starts in October, that's why... so FY 17 is 201610 - 201709 and so on...