Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

homersimpson
Contributor II

Question - Tutorial Master Calender

Hi,

i try this tutorial and his data: Understanding the Master Calendar (video)

I put the field "ProductID" to the worksheet. If i choose one ProductID there is a gap in the diagramm.

How can i show all month?

2016-01-29_14h26_46.jpg

regards,

Fritz

1 Solution

Accepted Solutions
MVP
MVP

Re: Question - Tutorial Master Calender

Hi,

If there is no data for the missing then QS/QV will skip those months in the axes.  To fill this you have try any of this two methods

1. Fill the missing months with 0s by using techniques in this two documents

How to populate a sparsely populated field

Generating Missing Data In QlikView

2. In Qlikview there is an option called Show All Values in Dimension Tab for each dimension, I am not sure whether the same exists in Qliksense, try if this option is there.  I think HIC has given workaround for this in below thread

QlikSense Dimension: show all values like QlikView?

Regards,

Jagan.

9 Replies
reddys310
Honored Contributor II

Re: Question - Tutorial Master Calender

Hi Fritz,

Are you using the dates in your table to generate the master calendar. If there are any dates missing you will not be able to do it. You need to fetch the Min(date) and Max(date) and generate the calender for all the dates between the min and max dates. Doing it that way you will not have a gap as above.

Check this script:

QuartersMap: 

MAPPING LOAD  

rowno() as Month, 

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

AUTOGENERATE (12); 

Let varMinDate = Num('01/01/2016'); 

Let varMaxDate = Num('31/12/2016');

 

TempCalendar: 

LOAD 

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

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

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

 

[Master Calendar]: 

Load 

               TempDate AS [Date], 

               week(TempDate) As [Week], 

               Year(TempDate) As [Year], 

               Month(TempDate) As [Month], 

               Day(TempDate) As [Day], 

               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;

store [Master Calendar] into [lib://WOC Debts Extracts/master_calander.qvd];

Drop Table TempCalendar;

homersimpson
Contributor II

Re: Question - Tutorial Master Calender

Hi Sangram,

thanks...i try your script and got this error:

2016-01-29_18h30_32.jpg

regards,

Fritz

reddys310
Honored Contributor II

Re: Question - Tutorial Master Calender

Hi Fritz,

Check if the variables varMinDate and varMaxDate have values in them while execution. As per your screen shot the value in varMaxDate is missing.

To rectify this change the date format of the variables to your system date format and give a try:

Let varMinDate = Num('01/01/2016');  -> change it your date format

Let varMaxDate = Num('31/12/2016'); -> change it your date format

Thanks,

Sangram

homersimpson
Contributor II

Re: Question - Tutorial Master Calender

Hi Sangram,

i change it to...

Let varMinDate = Num('01.01.2014');

Let varMaxDate = Num('31.12.2016');

...and got no error.

But there are still the gaps, if i choose a ProductID.

regards,

Fritz

Re: Question - Tutorial Master Calender

Probably you need to generate your missing data, see:

How to populate a sparsely populated field

Generating Missing Data In QlikView

- Marcus

MVP
MVP

Re: Question - Tutorial Master Calender

Hi,

If there is no data for the missing then QS/QV will skip those months in the axes.  To fill this you have try any of this two methods

1. Fill the missing months with 0s by using techniques in this two documents

How to populate a sparsely populated field

Generating Missing Data In QlikView

2. In Qlikview there is an option called Show All Values in Dimension Tab for each dimension, I am not sure whether the same exists in Qliksense, try if this option is there.  I think HIC has given workaround for this in below thread

QlikSense Dimension: show all values like QlikView?

Regards,

Jagan.

reddys310
Honored Contributor II

Re: Question - Tutorial Master Calender

Id the Master Calendar linked to your data model. Once linked, make use of the linked date field to plot the graph.

Thanks,

Sangram.

homersimpson
Contributor II

Re: Question - Tutorial Master Calender

Sorry, i´m not sure to understand what you said. "OrderID" should be in another table which is not connect with table "MasterCalendar"?

2016-02-02_18h44_57.jpg

regards,

Fritz

homersimpson
Contributor II

Re: Question - Tutorial Master Calender

Hi Jagan,

this helps:

Sum( Sales ) + 0 * Sum( {1} Sales ) - QlikSense Dimension: show all values like QlikView?

Thank you and to the others for your help.

regrads,

Fritz

Community Browser