problem with master calendar and filtered data

[#1]

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; 

[#2]

Here the two screenshots

[#3]

Anybody who can help me out here?

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

[r_wroblewski]

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

[#5]

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

[#6]

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]

Hi Jose

if possible please share some sample data.

Regards,

Ronny

[#8]

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]

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]

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