Sum with Conditions and Filter Panel

[Zaga_69]

Hi everyone!

I am struggling with the following issue: 

I would like to calculate two measures. The first one is called "total too late" which is the sum of all delivery items which have been delivered with delay for specific a period. The second one is called "Overdue" which is the sum of all delivery items which have not been delivered but do not belong to the period selected. Both measures make up the measure called “Total Delivery Items Not on Time”.

In the image below, the period selected is 2017-05 and the total number of delivery items are 3, two of them are "Overdue " and one is "Too late".

I was able to calculate in Qlik the one called "Total Too Late" with the following formula:

Sum({<DOT_STATUS={'Too Late'}>}YCOUNT)

However, I could not to calculate the second measure "Overdue" which has to sum the total delivery items that do not belong to the current period selected but has the flag “Too Late”.

It is important to mention that I have in my Dashboard in Qlik a Panel Filter for year and month. Then, the measure should take into account the month and year selected in the Filter Panel.

Any Idea?

Thanks

[Zaga_69]

Hi Chris,

The following formula is not working:

*---------------------------------------------------------------------------------------------------*

Sum({<DOT_STATUS={'Too Late'},PlannedGoodsMov={"<$(=Date(YearMonth))"},NotDeliveredYet={1}>}YCOUNT)  

+Sum({<DOT_STATUS={'Too Late'},PlannedGoodsMov={"<$(=Date(YearMonth))"},NotDeliveredYet={0},ActualGoodsMov={">$(=Date(YearMonth))"}>}YCOUNT) 

*---------------------------------------------------------------------------------------------------*

The first part should sum all delivered items which were placed late and for a period lower than the selected one (i.e. it is determined by YearMonth field).

The second one it is the same but with an additional condition ActualGoodsMov={">$(=Date(YearMonth))"}

The fields ActualGoodsMov and PlannedGoodsMov were created as follow:

*---------------------------------------------------------------------------------------------------*

Date(Date#(YWADAT, 'YYYYMMDD'),  'DD.MM.YYYY')                     as PlannedGoodsMov,

Date(Date#("0DATE",'YYYYMMDD'),  'DD.MM.YYYY')                       as ActualGoodsMov,

Date(Date#(YWADAT, 'YYYYMMDD'),  'DD.MM.YYYY')                         as DateKey;

*---------------------------------------------------------------------------------------------------*

In the Master Calendar the YearMonth was created as follow:

*---------------------------------------------------------------------------------------------------*

Load

 TempDate AS DateKey,

 week(TempDate) As Week,

 Year(TempDate) As Year,

 Month(TempDate) As Month,

 Day(TempDate) As Day,

 'Q' & ceil(month(TempDate) / 3) AS Quarter,

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

Date(monthstart(TempDate), 'YYYYMM') As YearMonth,

 WeekDay(TempDate) as WeekDay;

LOAD

 date(mindate + IterNo()) AS TempDate

 ,maxdate // Used in InYearToDate() above, but not kept

WHILE mindate + IterNo() <= maxdate;

LOAD

 min(FieldValue('DateKey', recno()))-1 as mindate,

//  '01.01.2012' as mindate,

 max(FieldValue('DateKey', recno())) as maxdate

//  '01.01.2025' as maxdate

AUTOGENERATE FieldValueCount('DateKey');

*---------------------------------------------------------------------------------------------------*

Here goes an example of items that for the period 201507 should be included in the second part of the formula, but it does not work:

• The item should has been delivered on 23.12.2014
• The item was delivered 09.09.2015

Considering we select the period 201507, this items should be calculated in the second part of the formula as the PlannedGodsMov is lower than the period selected 201507 and the ActualGoodsMov is higher than the period selected 201507. The items met those conditions, but the formula does not work. See image attached. 

Any hint?

Best regards

[chrismarlow]

Hi Edi,

Because you are making a selection on YearMonth, and DateKey associates/joins your calendar to your fact table, you would need to cancel it to not have that reduce the set, you can do that by adding YearMonth= to the set analysis (or possibly starting with 1, i.e. working from  the full population) or breaking the association (i.e. change the DateKey  field name in one or the other and the calendar becomes a data island).

Which way you go depends on the rest of what you want to model.

Cheers,

Chris.

[Zaga_69]

Hi Chris,

Thanks a lot. I broke the association between the fact table and the calendar and it works..

However, I am wondering how Qlik sense works.. Because usually, for example in Power Pivot, you need too link your fact table to your calendar (i.e. a tables which contains time dimensions like month, year, quarter, so on..).

Here, I have disconnected the calendar from the fact table and if I created a new sheet with YearMonth as Filter Pane and created a simple table on this sheet. The data on this table will be affected by the Filter Pane...but the Filter pane it is based on a field which is not connected to the model....so how it works??

Regards,

Edi

[chrismarlow]

Hi Edi,

I am not sure I follow. In the formulae you have you are in effect creating a link in the set analysis between the disconnected parts of the model. If your second set of visualisations (on same model) does not use similar then I don't understand, you would need to put up some detail.

My experience of QlikView is that generally you are trying to create a walled garden slightly bigger than the set of questions you have been tasked to dashboard, the benefit then being the associative behaviour/connectivity (which is reduced a bit when you do things like create data islands). Having done a bit of Power Query I suspect for your questions on this set of data you would end up with a number of more loosely connected queries that processed the data further, i.e. the modelling would be in those queries, rather than in the pivot table/charts (or Power Pivot), vs. Qlik, where you are doing more in the visualisations themselves.

Cheers,

Chris.