Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

Creating a data model that allows drill down

HI,

I'm new to working with Qlikview. I hope someone can offer some advice on a data model I'm trying to build. The end goal is a dashboard that compares region sales to region quotas by yr, qtr, month, day, and allows users to drill down to the from the summary data to the actual sales record.

For example when I open the dashboard I have total daily sales YTD for regions a, b,c next to the YTD quotas for each region. As I select a region, month, or date, my charts, tables, etc drill down and show the data specific to the selection. What my users want is to select a date and see the detail sales records for that date. Right now, I don't have that.

My data model is a SQL query that contains the detail the users want but rolls it up so I can present a summary of transactions per day by region with the regions sales quota and illustrate differences. My problem is whenever I try to add the detail the users want and incorporate the quota data my roll up to summarize the quota by month, region, year for the month is multiplied by the number of sales transaction per day per region.

I think what I need to do is break my query into 3 tables, a daily sales summary by region, a sales detail by region and a region sales quota. However, in Qlikview, I'm not sure how to link the tables together so I can drill down to the detial level of data.  CAn anyone offer some insight or point me to a similar example?

Tags (3)
5 Replies
datagrrl
Contributor III

Re: Creating a data model that allows drill down

Okay, everything I have looked at you have two options. One is a linked table, which I do not fully understand.

The method I used is actually pretty simple, even though it didn't seem quite right.

I have been an SSAS developer for years so I am used to being able to tell the model a little more than I can in Qlikview.

This fact table with mixed granularity worked well for me.

http://community.qlik.com/blogs/qlikviewdesignblog/2012/10/26/mixed-granularity

I am attaching a qvw and some mocked up data I used to demo this to some of my co-workers. I also have a word document I used to explain this.

It isn't your exact situation, but as far as I can tell from your question your real issue is mixed granularity.

Re: Creating a data model that allows drill down

There is a good overview of QlikView data modelling here http://community.qlik.com/docs/DOC-7343.


Not applicable

Re: Creating a data model that allows drill down

Exactly what I needed . Thank you.

Sincerely,

Bernard Smith | SQL Report Developer

datagrrl
Contributor III

Re: Creating a data model that allows drill down

If this ended up working for you can you mark it as the answer? If not please let me know and maybe I can help you.

Not applicable

Re: Creating a data model that allows drill down

Thank you for your help. I've been tied up with other tasks. I hope to try this out tonight. I think it will work.