Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

problem with master calendar and filtered data

Hi altogether,

I am using Qlik Sense Desktop 1.1.0 and have the following problem.

I want to show some data about orders. All data from a certain year is shown in a separate line chart. Important is, that also for the current year all twelve months should be shown, even if there is no order yet, i.e. June till December 2015 should be shown as zero values.

I created a master calendar and connected it to the order table by the same field name ‘OrderDate’.

When I start the app the first time everything looks fine (see Screen 1). In the first line Chart for the current year, the months June to Dec are shown with 0 values. But as soon as I click on the filter panel and select a product number, only the months are shown where the product was sold, but no longer the months June to Dec. (see screen 2).

You can also see in the table on the right side that shows all OrderDates, that without filtering there are no gaps in the dates. As soon the article is filtered there are gaps, i.e. some days are missing where the article was not sold.

Anybody has an idea what is going wrong?

Thanks

-------------------

The data for the line chart for the current year is :

Dimension Field:

MonthYear

Measure Field:

Sum({$<Year={2015}>} nNumber)

Script of order table:

LOAD kBestellung,

    tRechnung_kRechnung,

    tBenutzer_kBenutzer,

    tAdresse_kAdresse,

    tText_kText,

    tKunde_kKunde,

    cBestellNr,

    cType,

    date(floor(dErstellt)) as OrderDate, //cut off time and convert to date format again

   …

    MMYY,

    YYYY,

    MM;

SQL SELECT kBestellung,

    tRechnung_kRechnung,

    tBenutzer_kBenutzer,

    tAdresse_kAdresse,

    tText_kText,

    tKunde_kKunde,

    cBestellNr,

    cType,

    dErstellt,

   …

Right('00'+Convert(varchar, DATEPART(month, [dErstellt])), 2)+'/'+cast(DATEPART(yy, [dErstellt]) as varchar(4)) as MMYY,

    Year(dErstellt) as YYYY,

Right('00'+Convert(varchar, DATEPART(month, [dErstellt])), 2) as MM

FROM torder;

Master calendar script:

QuartersMap: 

MAPPING LOAD  

rowno() as Month,

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

AUTOGENERATE (12);

     

LET varMinDate = Floor(MakeDate(year(AddYears(Today(),-2)), 1 ,1)); //Heute vor 2 Jahren 1.1.13 /floor statt num

LET varMaxDate = Floor(MakeDate(year(Today()),12,31)); //Heute bis Ende dieses Jahres

     

TempCalendar: 

LOAD 

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

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

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

     

MasterCalendar:

Load 

  TempDate AS OrderDate, 

WeekYear(TempDate) &'/ S'& week(TempDate) AS id_Date,

  week(TempDate) As Week, 

  Year(TempDate) As Year, 

Month(TempDate) &'/'& Year(TempDate) AS MonthYear,

Month(TempDate) As Month,

  Day(TempDate) As Day, 

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; 

11 Replies
Not applicable
Author

Here the two screenshots

Screen1.jpgScreen2.jpg

Not applicable
Author

Anybody who can help me out here?

I spent many hours in finding a solution but had no success so far.

r_wroblewski
Partner - Creator III
Partner - Creator III

Hi Dirk,

what kind of Expression you use for graph ?

If you use simple Sum(Value) or Count(Value) you can try with

if(Not IsNull(Sum(Value)), Sum(Value), 0) or if(Not IsNull(Count(Value)), Count(Value), 0)

Let me know if this solve your problem.

If not, con you share some sample qvf with dummy data ?

Regards,

Ronny

Not applicable
Author

Hi Ronny,

thanks for looking into it.

I used as measure so far

Sum({$<Year={2015}>} nNumber)

Year is a field of the master calendar.

So I tried your proposal:

if(Not IsNull(Sum({$<Year={2015}>} nNumber)), Sum({$<Year={2015}>} nNumber), 0)

But has the same effect. All future months are showing with 0 in unfiltered state, but as soon as I filter for a product, the line chart only shows the months where a order exists.

I f you have another idea, I am happy to look at it. Besides that I will try to set up some sample data. But this will take a while as the date model is pretty complex.

Thanks

Dirk

Not applicable
Author

Hi Dirk,

Have you find a solution for this problem? I'm facing exactly the same issue with the Master Calendar with filtered data. Any additional information will be very usefull.

Thanks!

Jose

r_wroblewski
Partner - Creator III
Partner - Creator III

Hi Jose

if possible please share some sample data.

Regards,

Ronny

Not applicable
Author

Hi Ronny,

Thanks for your note. I've created a sample QVF with a small dataset; please see in attach. As you can see in the first chart, the Master Calendar is working properly, but in the second chart (with a filter applied) the chart is not displying all the months.

Any help will be much appreciated.

Regards,

Jose

r_wroblewski
Partner - Creator III
Partner - Creator III

Hi Jose

Sorry for my late reply.

Thanks for your sample data and I will try to check today and see what I can do.

Regards,

Ronny

Anonymous
Not applicable
Author

Hello,

This is normal behaviour. It is not only about Calendar. You need to add missing values per artikel to your fact table or try using intervalmatch() function for your join between calendar and the fact table.

Check these out first and let me know.

BR

Serhan