Announcements
Product Release Webinar: Qlik Insider airing December 6! REGISTER TODAY!
cancel
Showing results for
Did you mean:
Contributor

Problem with FY clients

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

1 Solution

Accepted Solutions
Partner - Creator III

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.

6 Replies
Partner - Creator III

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.

Contributor
Author

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!

Partner - Creator III

OK., see you later

Contributor
Author

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

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

Partner - Creator III

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.