topic Re: problem with master calendar and filtered data in App Development

problem with master calendar and filtered data

Mon, 25 May 2015 15:49:24 GMT

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;

Re: problem with master calendar and filtered data

Tue, 26 May 2015 06:15:35 GMT

Here the two screenshots

Re: problem with master calendar and filtered data

Wed, 27 May 2015 07:19:35 GMT

Anybody who can help me out here?

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

Re: problem with master calendar and filtered data

r_wroblewski — Thu, 28 May 2015 07:20:26 GMT

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

Re: problem with master calendar and filtered data

Thu, 28 May 2015 08:34:26 GMT

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

Re: problem with master calendar and filtered data

Sat, 29 Aug 2015 21:09:32 GMT

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

Re: problem with master calendar and filtered data

r_wroblewski — Tue, 01 Sep 2015 01:39:19 GMT

Hi Jose

if possible please share some sample data.

Regards,

Ronny

Re: problem with master calendar and filtered data

Wed, 02 Sep 2015 20:02:10 GMT

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

Re: problem with master calendar and filtered data

r_wroblewski — Fri, 11 Sep 2015 02:39:35 GMT

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

Re: problem with master calendar and filtered data

Anonymous — Fri, 11 Sep 2015 08:44:36 GMT

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.

Serhan

Re: problem with master calendar and filtered data

Fri, 11 Sep 2015 12:12:20 GMT

Hi Serhan,

Thanks for your help. I'm a bit confused how to do it. Do you have some example to share?

Regards,

Jose

Re: problem with master calendar and filtered data

Anonymous — Mon, 14 Sep 2015 07:29:36 GMT

Hello Jose,

Intervalmatch() helps you do something like a between join so that you are able to see the data on the specific dates even though there is no value entry. Useful especially for stock applications.

Attached is a sample qvw. Same approach for Qlik Sense as well. Just qvw came more handy.

Serhan