Previous Years and Next Year Calculation on multiple Year Types

[Ankhi]

Hi 

I have three types of Years in my Master Calendar. The Calendar Year, Fiscal Year and commercial Year.

I have to calculate , previous years actuals, current year actuals , and next year forecast using for Fiscal Year once and then Commercial Year.

I have done the calculations for Fiscal Year. Sample measures I am using for Fiscal Year are below.

Previous Year Calculations:

sum ({$<
[Cost Status] = {'ACT'},
Master.FYYear = ,
Master.Month = ,
Master.YearNum = {"<=$(=max(Master.YearNum)-1)"},
>}
Amount)

Current Year Actual (till the month selected in the month filter)

sum ({$<
[Cost Status] = {'ACT'},
Master.Month = ,
Master.Date = {"<=$(=max(Master.Date))"}
>}
Amount)

Next Year Forecast:

sum ({$<
[Cost Status] = {'TRC'},
Master.FYYear = ,
Master.Month = ,
Master.Quarter = ,
Master.YearNum = {"$(=max(Master.YearNum)+1)"}
>}
Amount)

All these above measures are giving correct output for the financial year calculations but for the commercial year only the current year actual is giving  correct output and the rest not. 

I have financial  year, commercial year and month as list boxes in my report. Was hoping the same measures would also work for commercial year but it is not working.

Can any one please help?

Thanks in advance.

Regards

Ankhi

[sunny_talwar]

@Ankhi would you be able to share a sample where we can see this?

[Ankhi]

Hi Sunny, 

Thanks for replying. What kind of sample are you exactly looking for ? I have attached some screenshots of the Calendar fields . If I select Fiscal Year FY20 the yearnum gets selected to only 2019, where as if I select Com year FY20, it shows two yearnum  2019/2018.  My Fiscal year starts from Jul-Jun and Com year starts from Apr-Mar.   I have also attached a front end of my app screenshot showing the measures that I need to calculate.

For fiscal year , I am using Yearnum to calculate the next year and next year+2 and so on calculations.

I was hoping the same should work for Com year also. But it is not. Only the measures Current Fy Actuals and Current FY TRC are working for Comyear.  And I am using DATE for that calculation. Please see below.

But the YearNum calculation which I am using to calculate the previous years and next years are not working for Com Year. 

Current FY Calculation/Actuals/Forecast(TRC)

sum ({$<
[Cost Status] = {'ACT'},
Master.Month = ,
Master.Date = {"<=$(=max(Master.Date))"}
>}
Amount)

The report needs to show the measure values for two different years, based on what the users select.

Regards

Ankhi

[sunny_talwar]

I was hoping for a qvw or qvf file where I can see your data and play around with it

[Ankhi]

Hi Sunny,

Sorry I cant share the qvw as  it is Client's confidential data. But just for your info the Calendar that I am using is a already built in routine. I just use it like below. Not sure though whether it helps in any way for the analysis

Call CreateCalendar( '<source_table>', '<source_date>', '<calendar_name>').

Regards

Ankhi

[Ankhi]

@sunny_talwar Also any other way I can help you with the analysis?

[sunny_talwar]

@Ankhi can you anonymize the data and share it?

[Brett_Bleess]

@Ankhi You can use the Settings\Document Properties\Scrambling tab to scramble any sensitive fields...

Regards,
Brett