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

[chrismarlow]

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.

[Zaga_69]

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

[chrismarlow]

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.

[Zaga_69]

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

[chrismarlow]

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.

[Zaga_69]

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

[chrismarlow]

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.

[Zaga_69]

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

[chrismarlow]

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.