topic Re: Set analysis in an expression in QlikView

Set analysis in an expression

Tue, 24 Feb 2015 15:18:33 GMT

I have a report where I am counting the number of distinct purchase orders that have been received and this is broken down into which shift has received it. When I use the expression in a text box it counts correctly, but where I have an issue is when I want to display the information in a graph by shift.

For example the number returned by the expression in the text box is 13. When I put this into a graph it returns 14. I know why it is doing this but I don't know how to fix it.

If a purchase order is received in across two shifts then it counts that purchase order twice.

The expression is:

count( {1 <Cal_Year= {$(vCurrentYear)}, Cal_Week = {$(vCurrentWeek)}> } DISTINCT PONumber)

As said above this returns the correct information when in a text box but in a graph it is returning the wrong data.

My dimension of shifts is broken into 3 types: Days, Afters and Nights. If a purchase order runs across from the Day shift to the Afters shift then it counts the purchase order twice, how do I correct this to return the same figure as the expression does in a text box?

Re: Set analysis in an expression

Tue, 24 Feb 2015 15:28:05 GMT

could you please attach the qvw?

Re: Set analysis in an expression

Karthik3 — Tue, 24 Feb 2015 15:28:38 GMT

Can you post a screenshot of the graph?

If a PO exists in two shifts(days and nights) then in which shift the PO should go?

Re: Set analysis in an expression

Tue, 24 Feb 2015 15:32:06 GMT

Hi,

I'm guessing the problem is that the PONumber exists in several different shift and then would show up once for each shift, although there is only one PONumber.

How is your total calculated? Sum of rows or expression total? Expression total should give you the correct one (setting on expression tab).

Re: Set analysis in an expression

Tue, 24 Feb 2015 15:48:10 GMT

qvw attached.

Re: Set analysis in an expression

Tue, 24 Feb 2015 15:55:57 GMT

Correct, if the PO exists in 2 shifts it should only really count in the first shift, even if it is finished on the second shift. The majority of our POs are received in on the day shift with only a small amount on the afters and night shifts.

The total is calculated in the expression.

Re: Set analysis in an expression

Tue, 24 Feb 2015 15:59:53 GMT

I think I answered your questions onto a different reply.

You are correct in that if the PO exists across both shifts it is counting as one for each shift even though there is only 1 PO.

Total is calculated from the expression shown in the original post.

Re: Set analysis in an expression

Oleg_Troyansky — Tue, 24 Feb 2015 16:35:16 GMT

I can suggest two possible solutions here:

1. Count distinct combinations of PO# and Shift:

count( {1 <Cal_Year= {$(vCurrentYear)}, Cal_Week = {$(vCurrentWeek)}> } DISTINCT PONumber&Shift)

2. Use Advanced Aggregation function AGGR() to perform your calculation at the shift level and then sum up the results:

sum(

AGGR(

count( {1 <Cal_Year= {$(vCurrentYear)}, Cal_Week = {$(vCurrentWeek)}> } DISTINCT PONumber)

, Shift)

)

Both calculations should produce consistent results.

cheers,

Oleg Troyansky

www.masterssummit.com - take your QlikView skills to the next level!

Re: Set analysis in an expression

Karthik3 — Tue, 24 Feb 2015 16:42:24 GMT

I don't find any PO number in both shifts so 14 is the correct total I guess.

Re: Set analysis in an expression

Tue, 24 Feb 2015 16:46:22 GMT

That is correct. Since posting I have done several reloads and the graph and text box for this week and today agree. When I expand to this month and last month then the figures do not match.

Re: Set analysis in an expression

Tue, 24 Feb 2015 16:47:19 GMT

Thanks, I will give this a try and report back.

Re: Set analysis in an expression

Tue, 24 Feb 2015 17:32:58 GMT

Hi Oleg

I tried both those options and I didn't get the answers I expected. I have attached the updated qvw.

My graph currently looks like this.

Total for this month is 97.

And the text box this

when I tried

count( {1 <Cal_Year= {$(vCurrentYear)}, Cal_Week = {$(vCurrentWeek)}> } DISTINCT PONumber&Shift)

it made no difference to the graph even after a reload.

When I tried

sum(

AGGR(

count( {1 <Cal_Year= {$(vCurrentYear)}, Cal_Week = {$(vCurrentWeek)}> } DISTINCT PONumber)

, Shift)

)

it did the following to my graph

Re: Set analysis in an expression

Tue, 24 Feb 2015 17:39:09 GMT

attached are the screen shots that didn't appear in the previous post.

After the AGGR expression and original text box

above is before the change to the expression

Re: Set analysis in an expression

Karthik3 — Tue, 24 Feb 2015 17:41:43 GMT

Don't use NOW() for calculating Current year and months, use TODAY(). Your application is executing every second because of NOW()

Re: Set analysis in an expression

Oleg_Troyansky — Tue, 24 Feb 2015 20:53:00 GMT

Steve,

I tried both of my suggestions in your document, and they both returned 97 for the current month, which is the expected count (see attached).

Cheers,

Oleg Troyansky

Re: Set analysis in an expression

Wed, 25 Feb 2015 10:22:19 GMT

Thanks Oleg, think I must have been having a meltdown by the time I tried what you suggested. Just to confirm should I put the same syntax on the expression for last month?

Re: Set analysis in an expression

Oleg_Troyansky — Wed, 25 Feb 2015 14:36:15 GMT

Steve - yes, the same logic, just change the filters to the last month filters.

Re: Set analysis in an expression

praveenak — Wed, 25 Feb 2015 14:55:00 GMT

Hi Steve,

I see that there is NO PONumber exists common to both 'Afters' and 'Days' Shifts.

Hence it is showing 14 both in text object as well in graph for Current Week.

And 22 in both text object as well in graph for Last Week.

You can also see the result of below query returns ZERO which means there is no common PONumber existing in both 'Afters' and 'Days' Shifts.

=count({<Shift={'Afters'}>*<Shift={'Days'}>}@PONumber)

As well you can the below pivot table has no PONumber containing both the shifts.

Re: Set analysis in an expression

Wed, 25 Feb 2015 15:05:39 GMT

That's great, thanks Oleg.