Master Calendar enables all dates to display, but not when I filter!?

[Anonymous]

Hi - I have billings data for 2 years.  On some days, there were no billings, but I built a Master Calendar and this gives me every date and my charts correctly show the days where there were zero billings.

However, as soon as I apply any filter, such as Region, all my charts skip the days with no billings.

Any advice would be appreciated.

Steven

[settu_periasamy]

Hi,

However, as soon as I apply any filter, such as Region, all my charts skip the days with no billings.

Do you mean, the chart doesn't display the Date ?..May be this

check the 'Show All values' in dimension Tab ?

[Anonymous]

Thanks - yes, the date is not displayed if there were no billings.  If I check 'Show All Values', I would see every date for 2 years, but I want to be able to pick just one month withing a single year.  When I do that, I do see every date if I don't filter anything else, but as soon as I filter on Region, Customer, etc., the dates with no billings are not displayed.

Steven

[settu_periasamy]

Is it possible to post the sample qvw?

[jagan]

Hi Steven,

Since there are no records for the selected Region it is not showing the dates with no billing, this is the known scenario in Qlikview.  To overcome this you have to insert dummy records with 0 billing for the missing dates by region, like this you have to do for the remaining fields.

Hope this helps you.

Regards,

jagan.

[Anonymous]

Certainly:

Billings:

LOAD MaterialCustomerIDHybrid,

     [Billing Doc],

     date(date#([Billing Date], 'YYYYMMDD')) as [Billing Date],

     [Customer ID],

     [SO Created On],

     Qty as Quantity,

     [Sales Order],

     [SO Type],

     [SO Item],

     Material,

     MaterialDesc as [Material Description],

     Plant,

     Plant & [Billing Date] as PDID,

     [Customer Material],

     Category

FROM

(qvd);

QuartersMap: 

MAPPING LOAD  

rowno() as Month, 

'Q' & Ceil (rowno()/3) as Quarter 

AUTOGENERATE (12); 

Temp: 

Load 

               min([Billing Date]) as minDate, 

               max([Billing Date]) as maxDate 

Resident Billings; 

Let varMinDate = Num(Peek('minDate', 0, 'Temp')); 

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp')); 

DROP Table Temp; 

TempCalendar: 

LOAD 

               $(varMinDate) + Iterno()-1 As Num, 

               Date($(varMinDate) + IterNo() - 1) as TempDate 

               AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate); 

MasterCalendar: 

Load 

               TempDate AS [Billing Date], 

               week(TempDate) As Week, 

               Year(TempDate) As Year, 

               Month(TempDate) As Month, 

               Day(TempDate) As Day, 

               Year(TempDate) & Week(TempDate) & Day(TempDate) as YWD,

               YeartoDate(TempDate)*-1 as CurYTDFlag, 

               YeartoDate(TempDate,-1)*-1 as LastYTDFlag, 

               inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12, 

               date(monthstart(TempDate), 'MMM-YYYY') as MonthYear, 

               ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter, 

               Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear, 

               WeekDay(TempDate) as WeekDay 

Resident TempCalendar 

Order By TempDate ASC; 

Drop Table TempCalendar; 

[Anonymous]

Thanks Jagan - I had believed that the Master Calendar was the way round this, and it does work for the 'global' view (no filters), but I will now look at creating the dummy records.

[settu_periasamy]

Script seems to be fine. For the testing purpose, can you post the Sample QVW?