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]

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

[#3]

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

[#4]

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

[Karthik3]

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

[Oleg_Troyansky · Accepted solution]

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

[#7]

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?

[Oleg_Troyansky]

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

[praveenak]

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.

[#10]

That's great, thanks Oleg.