topic Data on different aggregation levels in QlikView

Data on different aggregation levels

chriys1337 — Thu, 18 Jan 2018 11:42:29 GMT

Hi,

I am using a fact table where my performance is stated in €. In this fact table there is a key to Calendar, and the Key is made with autonumber(Date), so it is on the most possible granularity.

Unfortunately I don't know how to add the Target data, which I have on weekly basis to this scheme, due to the Key which is on daily basis and not on weekly!!!

I am sure this is issue which can be solved easily for experts.

Can anybody give me a tip please?

Kind Regards,

Chris

Re: Data on different aggregation levels

Gysbert_Wassenaar — Thu, 18 Jan 2018 12:16:18 GMT

See this blog post: Fact Table with Mixed Granularity

Re: Data on different aggregation levels

petter — Thu, 18 Jan 2018 12:28:59 GMT

In a simple modification of your data model you could do something like this:

That way the associative model helps you get hold of the weekly target however you select in your application. But you have to make the expressions accordingly so you don't sum up the weekly target for each day of the week of course.

Re: Data on different aggregation levels

stabben23 — Thu, 18 Jan 2018 12:32:32 GMT

First of all I hope you have weekyear or yearweek. if so create a date Field of this, the lowest granularity here will be the date when the week start. When you have the weekstart Field, concatenate Target data With Fact table With this key so it will be connected with Your calendar.

In presentation view, GUI, dont present day in a listbox, week should be the last Level.

weekstart(WeekName(MakeWeekDate(Left('201801',4), Right('201801',2)))) as Datekeytocalendar replace the date here With Your yearweek Field. Also wrap autonumber around to get same format as in Fact.

you can test this in a textbox before you script it in.