Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

qlikdash
Contributor II

How to Create a Master Calendar with 3 Fact Tables

I have 3 tables that has records added monthly. I am trying to set up master calendar

to pick up values month wise. The sample has data for Dec and Jan only.

Order: The Req is that, for ProductA for Dec 2015, it sud plug Value X and Value Y for Jan 2016

Group: The Req is that for ProductA, for Dec 2015, it sud plug Attribute 10 for A and attribute 40 for Jan 2016

in Expn 1, I am counting Product leased when Value is "X" for dec 2015 or Jan 2016.

The Value of X is assigned in Order Table. So when I select Dec 2015, Expn 1 for XYZ

sud be  3, but it shows 5 at all times which is incorrect. 3, because A has "X" in 2015

from Order table and A was sold 3 times in 2015, shown by Sales table

How do I configure this with a master calendar.

For Expn 2,the total for year 2015 sud be 50 but its showing 210 and does not respond to the year filter.

50 bcoz under Group XYZ, Product A was sold 3 times (10 * 3) and B was sold once (20 *1)

pls see the excel file and qvw attached. !

1 Solution

Accepted Solutions

Re: How to Create a Master Calendar with 3 Fact Tables

HI,

Have a look at the attach application for solution..

Regards,

Kaushik Solanki

9 Replies
balrajahlawat
Esteemed Contributor

Re: How to Create a Master Calendar with 3 Fact Tables

MVP
MVP

Re: How to Create a Master Calendar with 3 Fact Tables

The best way that I find is to concatenate all fact tables into a single table, and rename and augment columns as required.  This tends to work much better than having lots of table.

So from Sales:

Fact:

LOAD

'Sales' as RowType,

Product,

Group,

[Sales Date] as Date

...

Then from Order:

CONCATENATE(Fact)

LOAD

'Order' as RowType,

Product,

[Order Date] as Date

As you don't have Group in your Order table, when you select a Group you will not see any orders (is this correct?).  This can be fixed either by looking up the group when you load orders, or by ignoring selections on Group using Set Analysis when looking at Order values.

If you have large amounts of data and QVDs you will need to ensure you make your loads Optimised.  This can be done by keeping columns in all concatenated tables identical.

Other techniques for multiple fact tables and a single calendar are Link Tables and putting your date table in a Data Island.

Hope that all makes sense and is of help.

Steve

Re: How to Create a Master Calendar with 3 Fact Tables

HI,

Have a look at the attach application for solution..

Regards,

Kaushik Solanki

vsudhakar
Contributor III

Re: How to Create a Master Calendar with 3 Fact Tables

In all tables Date fields as Date and taken Date columns in Master Calendar than it is solve it

qlikdash
Contributor II

Re: How to Create a Master Calendar with 3 Fact Tables

I have simplified the Question and app to go step by step. Pls see the attached QVW.

In the app, 1st table is how many products were sold in 2015? ans sud be 4. why is it showing 6?

I think I have the link table correctly place with Type as Sales which is used in the expn.

Pls comment what I am missing.

order.PNGSales.PNG !

qlikdash
Contributor II

Re: How to Create a Master Calendar with 3 Fact Tables

here is the qvw

vsudhakar
Contributor III

Re: How to Create a Master Calendar with 3 Fact Tables

Date:

load9

Order date as Date,

Resident Order;

Date1:

Load

Sales Date as Date

Resident to Sales

Calendar

load 

Month(Date) as Month,

Year(Date) as Year,

Week(Date) as Week,

Day(Date) as Day,

'Q' & Ceil(Month(Date)/3) as Quarter,

Year(Date) & Ceil(Month(Date)/3)  as Quarter Year

qlikdash
Contributor II

Re: How to Create a Master Calendar with 3 Fact Tables

Thanks Everyone

vsudhakar
Contributor III

Re: How to Create a Master Calendar with 3 Fact Tables

Try this one

  1. QuartersMap: 
  2. MAPPING LOAD  
  3. rowno() as Month, 
  4. 'Q' & Ceil (rowno()/3) as Quarter 
  5. AUTOGENERATE (12); 
  6.  
  7. Temp: 
  8. Load 
  9.                min(OrderDate) as minDate, 
  10.                max(OrderDate) as maxDate 
  11. Resident Orders; 
  12.  
  13. Let varMinDate = Num(Peek('minDate', 0, 'Temp')); 
  14. Let varMaxDate = Num(Peek('maxDate', 0, 'Temp')); 
  15. DROP Table Temp; 
  16.  
  17. TempCalendar: 
  18. LOAD 
  19.                $(varMinDate) + Iterno()-1 As Num, 
  20.                Date($(varMinDate) + IterNo() - 1) as TempDate 
  21.                AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate); 
  22.  
  23. MasterCalendar: 
  24. Load 
  25.                TempDate AS OrderDate, 
  26.                week(TempDate) As Week, 
  27.                Year(TempDate) As Year, 
  28.                Month(TempDate) As Month, 
  29.                Day(TempDate) As Day, 
  30.                YeartoDate(TempDate)*-1 as CurYTDFlag, 
  31.                YeartoDate(TempDate,-1)*-1 as LastYTDFlag, 
  32.                inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12, 
  33.                date(monthstart(TempDate), 'MMM-YYYY') as MonthYear, 
  34.                ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter, 
  35.                Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear, 
  36.                WeekDay(TempDate) as WeekDay 
  37. Resident TempCalendar 
  38. Order By TempDate ASC; 
  39. Drop Table TempCalendar; 
Community Browser