topic Re: Sum with Conditions and Filter Panel in QlikView

Sum with Conditions and Filter Panel

Zaga_69 — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Sum with Conditions and Filter Panel

chrismarlow — Fri, 23 Nov 2018 15:18:39 GMT

Hi,

There will be other ways to do this, but you could try something like in the attached.

Key parts;

1) Adding YearMonth to your data to make it easy to select the records before (as that seems to be where the 2 comes from ...) - (Year*100)+ Month as YearMonth

2) Setting vSelectedMonthYear as variable =(Year*100) + Month means the set analysis will be cleaner

3) Set analysis to cancel selections on Year/Month & apply < selection to YearMonth =Sum({<DOT_STATUS={'Too Late'},Year=,Month=,YearMonth={'<$(=vSelectedYearMonth)'}>}Ycount), showing in the lower text object

Hope that helps.

Cheers,

Chris.

Re: Sum with Conditions and Filter Panel

Zaga_69 — Mon, 26 Nov 2018 12:22:44 GMT

Hi Chris

Thank you for your contribution.

1. I have created a new field called "YearMonth" in the Master Calendar:

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

2. I used this field as Filter Pane in my Dashboard.

3. I tried to calculate the total number of delivery items with the flag "Too Late" for a specific period. The formula does not have any mistake, however it overestimated the total number of delivery items.

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

+Sum({<DOT_STATUS={'Too Late'},YearMonth={$(=YearMonth)}>}YCOUNT) //Too Late for month

4. I did not get completely your idea with the variable. I do not now know if just putting the field "YearMonth" in the formula it will work when the user select specific period.

Best regards and thank again,

Edi

Re: Sum with Conditions and Filter Panel

chrismarlow — Mon, 26 Nov 2018 13:04:14 GMT

Edi,

Adding to the master calendar and using that to filter, rather than use both month & year & bolt together, is a better solution than mine.

I think two tweaks and you are there;

1) I think you need < not <= in your set as you otherwise might double count in current month?

2) Although YearMonth is formatted YYYYMM it is still a date, so in the set analysis from (1) you need to format as a date, so;

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

This is confusing as it works for the second piece of set analysis, but then it is just looking for a match.

Does that give you the right numbers?

Cheers,

Chris.

Re: Sum with Conditions and Filter Panel

Zaga_69 — Mon, 26 Nov 2018 13:28:26 GMT

Hi Chris,

Thanks a lot for your fast reply. I really appreciate that.

Now, the number looks more appropriate, however I still need to make some tests 🙂

I have an additional question. In the following formula, the part with bold font, should I also add the Date function?

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

+Sum({<DOT_STATUS={'Too Late'},YearMonth={$(=YearMonth)}>}YCOUNT) //Too Late for month selected

Best regards,

Edi

Re: Sum with Conditions and Filter Panel

chrismarlow — Mon, 26 Nov 2018 13:56:07 GMT

Edi,

I don't think you need to, at least in what I am seeing it works without (although as you say you need to test more, I find dates & set analysis/QlikView generally fiddly, although I am sure there is ultimately logic to it);

Cheers,

Chris.

Re: Sum with Conditions and Filter Panel

Zaga_69 — Mon, 26 Nov 2018 14:18:21 GMT

Chris,

I have an additional question. Now I am struggling in creating the evolution of those measures. I would like to do a Line Graph.

As the overdue measure "Not on Time" calculates the number of delivered items with the flag "Too Late" or "Too Early" for a specific period of time, how can I do a Line Graph to see the evolution?

Should I create a measure by each month? I know it is not an efficient solution, but how to implement the "Overdue" logic..

Any Idea?

Best regards

Re: Sum with Conditions and Filter Panel

chrismarlow — Mon, 26 Nov 2018 16:47:13 GMT

Hi,

I think you can, without resorting to a function per month, but I wonder if it will show you what you are expecting if you continue with these functions rather than rethinking what it should show.

So your first record from your initial post (6/27/2016) - would you want that to show in every subsequent month as it is still open?

Similarly, something that was open for a couple of months, but now closed, you do need to show that in your line chart for each month, or just its initial month?

Cheers,

Chris.

Re: Sum with Conditions and Filter Panel

Zaga_69 — Tue, 27 Nov 2018 12:12:51 GMT

Hi Chris,

basically what I want to to do is sth like this:

when the user select the YearMonth in the filter, automatically the cells should be populated. If the user selected 201501, the cell Month-12 should calculate the measure for the period 201401 (12 months before) and so on...

I think I should create a measure by month....but it will involve many measures....

Best regards,

Edi

Re: Sum with Conditions and Filter Panel

chrismarlow — Tue, 27 Nov 2018 15:11:35 GMT

Hi,

You shouldn't need different measures by date. So you would either use your existing date field as a dimension (& change your formulae), or create a calendar table (and again change your formulae), or you could use a ValueLoop to create a synthetic dimension & use that (and again change your formulae).

Which is best depends on how each record that is tagged appears in the measures (in your time series view I am not sure you want to show the same record in more than one month - in which case I think you just use your existing date field).

Cheers,

Chris.

Re: Sum with Conditions and Filter Panel

Zaga_69 — Thu, 29 Nov 2018 13:41:21 GMT

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

Re: Sum with Conditions and Filter Panel

chrismarlow — Fri, 30 Nov 2018 15:40:07 GMT

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.

Re: Sum with Conditions and Filter Panel

Zaga_69 — Fri, 30 Nov 2018 16:01:52 GMT

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

Re: Sum with Conditions and Filter Panel

chrismarlow — Fri, 30 Nov 2018 16:36:31 GMT

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.