Dealing with dynamic reporting periods across multiple customers

[gileswalker]

Hi Qlik Forum

Hoping someone can help with something I have found hard to research for other user examples, mainly as I have no idea of key words/topics to search by!  Hopefully some more experienced users can give some guidance.

I'm using Qlik Sense 3.2 by the way.

I am setting up a Qlik App to eventually flow through to nPrinting.  In the data extract I will get from our ERP system, will be a bunch of shipping data for a freight company and I need to create KPIs and key measures for our customers. 

The crux of the situation is that quite a lot of our customers don't have Jan-Dec reporting periods.  They use a multitude of other reporting cycles, like Apr-Mar, or Sept-Aug etc.  Luckily our customer master data table contains a field which indicates the month the customers reporting period starts which I can pull into Sense, so I have something to work from.

What I can't work out is how to leverage that field and get QlikSense to essentially 'allocate' that month to the appropriate reporting cycle.

All reporting cycles cover a 12 month period.

An example:

Today is 29th May 2017, and if customer A's reporting period start month is Aug (for an annual reporting cycle of Aug-Jul), then Qlik needs to calculate that today belongs to the reporting period of Aug-16 to Jul-17.  Following on, if I was then using the calendar measures function to look at YTD, QTD current month versus same month last year etc, Qlik would need to be able to dynamically figure out the appropriate period totals correctly.

In the same example as above, if the customer B's reporting period start month is Apr (for an annual reporting cycle of Apr-Mar), then today's date of 29th May 2017 would drop today into the reporting cycle of Apr-17 to Mar18.

So you can see that by having customers with different reporting cycles, today's date can drop into a different reporting windows.

If anyone can help direct me to any similar examples where tips have been provided, or offer any advice as to how to approach this, I would be very grateful.

Thanks in advance

Giles

[pabloviera]

Hi Giles,

we've faced a similar situation dealing with the interpretation of a season between different markets in the hospitality industry regarding how contracts are drawn up.

We've got the regular seasons (Summer is May to Oct, Winter is Nov to April), but in Benelux Summer is April to Oct, and in Nordic countries Winter is Oct to April.

What we have is 3 different fields in the master calendar (One for each kind of season) and users now what to select according to what market they're analyzing. This is the regular season example, and you just change the months to get the other seasons:

if(myMonth=1 OR myMonth=2 OR myMonth=3 OR myMonth=4,'Winter '& (year("myDate")-1)&'/'&(year("myDate")),
    if(myMonth=11 OR myMonth=12,'Winter '& myYear &'/'& (year("myDate")+1),'Summer '& myYear)) as "Season"

I guess your equivalent would be having as many different years as you need.

At the end of the day this is suboptimal and creates many fields and duplicate charts, I don't know if somebody has come up with a better solution.

Regards