Count first sorted value based on additional selection

[peterderrington]

Hi, 

I'm having some difficulties getting the syntax right for something i'm trying to calculate.

One of my graphs displays the first instance that something occurs within a particular location - i currently choose that location as a filter.

However i am now in the position whereby i need to both add another filter and count the instances but i don't want to have to do that by selecting the filter but rather build it in to the original expression.

My original expression is:

//Time(FirstSortedValue(DISTINCT [Sent for Time],ID,1))

But i now need it to count the same first sorted value but only if the field '[Session(1=AM 2=PM 3=Eve)]' is equal to '1' and if it started before 09:01:00

Can anyone help.

The closest I've got is:

Count(FirstSortedValue(DISTINCT{<[Session(1=AM 2=PM 3=Eve)]={'1'}>}[Sent for Time]<='00:09:00',ID,1))

but the expression is clearly wrong.

[jaibau1993]

Hi!

Try something like

Count(FirstSortedValue(DISTINCT{<[Session(1=AM 2=PM 3=Eve)]={'1'}, [Sent for Time] = {"<=$(=Time('09:00'))"}>}[Sent for Time],ID,1))

Where I sopposed that your field [Sent for Time] is properly interpreted as a "time" field with format 'HH:MM:SS'. If it does not work, please try to share a sample APP so we can test (with dates and times and so on there is often errors in interpretation or formats)

Regards,

Jaime.

[peterderrington]

Hi Jamie, 

thanks for getting back so quickly. Unfortunately that doesn't seem to work, i imagine that you're right and its probably a formatting issue that's stopping it from working.

I'll look to create a version i can upload so you can see it. at the moment its just a simple straight chart so i can see if the calculations work (the count would either return a 1 or 0 as i'm concentrating on one location), ultimately it may look more like a run chart or similar so i can see how often over the weeks / months they run to schedule.

[peterderrington]

A version of the app.

Thanks.

[jaibau1993]

Hi Peter!

Thanks for the test APP. I realized that I made a mistaked when I copy-pasted your expression to suggest you a solution. Please, try the following, it works on the test app:

Time(FirstSortedValue(Distinct {<[Session(1=AM 2=PM 3=Eve)]={'1'}, [Sent for Time] = {">9:00"}>}[Sent for Time],ID,1))

Regards,

Jaime.

[peterderrington]

Hi, 

Sorry for the delay - Sickness!

That expression does work for identifying the first instance under a '1' condition which is great but i need to be able to count the instances; a 1 for if it conforms to the condition, 0 if not. The idea being so i can state how often that condition is met over a period of time.

Any idea?

Thanks.

[jaibau1993 · Accepted solution]

Hi!

Okey, I did not understand what you were trying to achieve. Just use a Count function and maintain the set expression:

Count({<[Session(1=AM 2=PM 3=Eve)]={'1'}, [Sent for Time] = {">9:00"}>} [Sent for Time])

Regards,

Jaime.

[peterderrington]

Fantastic, thank you!

Its working perfectly now and i have an extra expression that i hadn't even realised that i needed so its a double win.

Thank You!!

[peterderrington]

This is probably unlikely but is it possible to get it to return a 'Yes' / 'No' on the count, eg 'No' if it counts '0' and 'Yes' if it counts a '1'+ ?

[jaibau1993]

Yes, of course you can. Try something as the following:

if( Count({<[Session(1=AM 2=PM 3=Eve)]={'1'}, [Sent for Time] = {">9:00"}>} [Sent for Time]), 'Yes', 'No')

Regards,

Jaime.