topic Re: Previous Years and Next Year Calculation on multiple Year Types in QlikView

Previous Years and Next Year Calculation on multiple Year Types

Ankhi — Sat, 16 Nov 2024 18:01:01 GMT

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

Re: Previous Years and Next Year Calculation on multiple Year Types

sunny_talwar — Mon, 05 Oct 2020 13:05:52 GMT

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

Re: Previous Years and Next Year Calculation on multiple Year Types

Ankhi — Mon, 05 Oct 2020 13:35:18 GMT

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

Re: Previous Years and Next Year Calculation on multiple Year Types

sunny_talwar — Mon, 05 Oct 2020 13:52:52 GMT

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

Re: Previous Years and Next Year Calculation on multiple Year Types

Ankhi — Mon, 05 Oct 2020 15:12:58 GMT

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

Re: Previous Years and Next Year Calculation on multiple Year Types

Ankhi — Mon, 05 Oct 2020 15:14:00 GMT

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

Re: Previous Years and Next Year Calculation on multiple Year Types

sunny_talwar — Mon, 05 Oct 2020 15:58:40 GMT

@Ankhi can you anonymize the data and share it?

Re: Previous Years and Next Year Calculation on multiple Year Types

Brett_Bleess — Thu, 22 Oct 2020 15:02:27 GMT

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

Regards,
Brett