Set analysis in an expression

[#1]

Hi

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?

[#2]

could you please attach the qvw?

[Karthik3]

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?

[#4]

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).

[#5]

Hi

qvw attached.

[#6]

Hi

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.

[#7]

Hi

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.

[Oleg_Troyansky]

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!

[Karthik3]

I

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

[#10]

Hi

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.