Problem with FY clients

[rutgerzwaal]

Hi,

My name is Rutger and I've been working with Qlikview for a few months now.

Till now I've been able to solve all the problems I ran into, but now I'm stuck, so I hope the community can help me out.

Let me explain the problem.

I work for a tv broadcaster, and we have lots of clients. These clients all have year contracts, and they can buy airtime on a monthly base.

In the report I'm working on we want to check current spending's year to month (hope that's the correct way to say that),  vs the spending's last year in the same period.

I've been able to do for clients that have a regular contract (see attached app), but we also have quite a few clients with a fiscal year.

For the clients with a FY we don't want to compare spending from January, but from the start of the contract.

I've seen the option of changing the mastercalender http://community.qlik.com/blogs/qlikviewdesignblog/2013/05/28/fiscal-year,

And this works fine, but it's only for one client, and we want to calculate all the clients at once.

I've created an example of our situation with two clients (see the two attachments).

Hopefully someone can help me out.

Best regards,

Rutger

[vadimtsushko · Accepted solution]

Hi Rutger.

Good example you have prepared

I propose solution based on additional dimension in fact table for fiscal period (FiscalYearMonth)

See attachment for a (mostly working) solution. To your expressions "Year to month" and "Last year to month" I've added "Year to month (fiscal)" and "Last year to month (fiscal)" for easy comparison.

Best regards, Vadim.

[rutgerzwaal]

Thank you for replying Vadim, much appreciated!

To bad I won't be able to to check your solution today or tommorow, but I will get back to you on Thursday.

Have a great day!

[vadimtsushko]

OK., see you later

[rutgerzwaal]

Thank you Vadim, your solution works indeed. Great job!

I've been looking at what you did and I think I understand.

I've added

If(ClientFiscalYearStartMonth = 3, AddMonths(MonthStart(Date),-2) etc...

to BudgetTmp load script in case other FY clients pop up.

The next step that I want to do is add the Volume (on the contracts tab) to the year to month comparison.

I want to be able to check the contract vs the spendings, but I can't figure out how to do this.

Any idea how to add the contract year and Volume correctly to the fact table?

Best regards,

Rutger

[vadimtsushko]

Hi, Rutgers.

Glad it works for you. Regarding your second question - would you mind make a new thread for it and give a detailed example - what kind of report you have to get. Maybe in Excel. IF nobody would give you answer earlier, I would try to look at it at weekend.

Best regards, Vadim

[rutgerzwaal]

Hi Vadim,

I will do that.

Best regards,

Rutger