topic Re: Financial YTD with 13 Periods in QlikView

Financial YTD with 13 Periods

Wed, 06 Jan 2016 13:56:27 GMT

Hi There,

I am used to creating dynamic YTD and MTD charts using a standard calendar. Using Functions like addmonths, (max(Year)-1) etc.

I need to create a Previous Year YTD function involving a 13 period Financial Year without using calendar functions.

My Previous Year Expression looks like this:

Sum({$<FinYear = {$(#=(Left(max(FinYearPeriod),4)-1))},FinPeriod = {$(#=right(max(FinYearPeriod),2))}>}[Line Sales])

FinYearPeriod looks like this:

201501

201502

201503

201504

201505

201506

201507

201508

201509

201510

201511

201512

201513

201601 etc etc

I also need to create an expression showing the max 3 Period Sales, how would i do this without Addmonths or being able to minus. As far as i see it Qlikview sees my YearPeriod as a number so if we are in Period 201601 and i say max(YearPeriod)-1 this will equal 201600.

Any Help would be appreciated.

Thanks,

Richard

Re: Financial YTD with 13 Periods

Wed, 06 Jan 2016 14:08:51 GMT

FYI my Previous Year Period expression does not work

Thanks

Re: Financial YTD with 13 Periods

marcus_sommer — Wed, 06 Jan 2016 14:12:33 GMT

Maybe you could apply a second master-calendar like this one: 4-5-4 Master Calendar and use them to simplify your period-matchings maybe with user-defined functions (variables with parameter) or you could flag those FYTD / PFYTD in a similar way like descripted in various postings here: How to use - Master-Calendar and Date-Values

- Marcus

Re: Financial YTD with 13 Periods

Wed, 06 Jan 2016 14:44:25 GMT

Hi Marcus,

I already have a second master calendar which is very specific to my client, there is no constant pattern as sometimes they add an extra week to one of their periods etc, this i have created in excel.

As for the your (very well put together) thread. I have actually stumbled upon this a couple of times and it has helped me in the past. I still cannot find anything regarding a 13 period Financial Year in their.

You are clearly an expert in his topic. have you worked on a Financial Calendar like this before?

Thanks again

Re: Financial YTD with 13 Periods

marcus_sommer — Wed, 06 Jan 2016 15:00:06 GMT

This meant the periods are not completely predictable and the periods could differ over the years, right? I'm not sure but I think I would try to create such flags with the logic of the function daynumberofyear() - applying it by not calendar years with a peek/previous approach - and counting then backwards your needed days and/or trying to use this within a set analysis.

But before I would consider to extend those manually excel-file to such flags - I think to filter such excel-list and set these flags will only take a few minutes (it's not nicely but practically).

- Marcus

Re: Financial YTD with 13 Periods

Thu, 07 Jan 2016 06:24:25 GMT

Hi Marcus,

I see what you're saying. But i still need to measure example Fin Year 2016 & Fin Period 01 against Fin Year 2015 & Fin Period 01 Sales. It isn't a set amount of days, it is always Period vs Period.. Even if the current Fin Year period has 7 days more data...

I don't think i'm far off with my expression, it works in a text box, i probably need to persist a bit more and play around a bit with it!

Thanks,

Richard

Re: Financial YTD with 13 Periods

Anonymous — Thu, 07 Jan 2016 06:36:47 GMT

Hi Richard,

Have a look at this post Re: How to extract period Wise Qvds

Regards

Neetha

Re: Financial YTD with 13 Periods

marcus_sommer — Thu, 07 Jan 2016 06:43:15 GMT

Hi Richard,

I think it's easier then thought. If you have this financial period in a master-calendar you could use most of the logic from the normal calendar to create flags or the use of a max(Period) and counting backwards and forwards - of course calendar-functions like addmonths() or inmonthtodate() won't work but they aren't needed.

Beside your financial-period I would create a financial -year, -month and a monthcounter - autonumber(FinancialPeriod) - and then should be the same logics applicable like in normal calendars.

- Marcus

Re: Financial YTD with 13 Periods

Thu, 07 Jan 2016 08:56:40 GMT

Thanks For Your Help Marcus.

It was far simpler than i originally thought (as it usually is) as a number YearPeriod is actually easy to work with. Previous Month YTD Sales is an easy formula:

sum({<FinYearPeriod = {$(#=max(FinYearPeriod)-100)}>}Sales)

FinYearPeriod example 201601 - with the above example becomes 201501.

Apologies for the unnecessary hassle.

Cheers