topic Re: Problem with FY clients in QlikView

Problem with FY clients

rutgerzwaal — Mon, 21 Jul 2014 11:56:08 GMT

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

Re: Problem with FY clients

vadimtsushko — Tue, 22 Jul 2014 07:53:36 GMT

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.

Re: Problem with FY clients

rutgerzwaal — Tue, 22 Jul 2014 08:57:54 GMT

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!

Re: Problem with FY clients

vadimtsushko — Tue, 22 Jul 2014 11:06:22 GMT

OK., see you later

Re: Problem with FY clients

rutgerzwaal — Thu, 24 Jul 2014 15:02:36 GMT

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

Re: Problem with FY clients

vadimtsushko — Thu, 24 Jul 2014 16:01:27 GMT

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

Re: Problem with FY clients

rutgerzwaal — Fri, 25 Jul 2014 06:42:18 GMT

Hi Vadim,

I will do that.

Best regards,

Rutger