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.

[peterderrington]

Fabulous, Thank you, just need to have a play about to see if i can manage to create a graph (ideally a sort of Run Chart) to display that yes/no response over time

[peterderrington]

think i'll have to start a new thread really but.....

I can only display anything in a Straight chart for this yes/no data, there isn't a dimension that is relevant i.e. yes/no to allow it to display anything in a pie chart or similar, any quick suggestions before i start a new thread?

[jaibau1993]

Hi Peter! sorry for the delay, last friday was holiday here in Spain 🙂

I am not sure about what kind of visualization you'd want to build. You can create really complex 'calculated dimensions' for your charts but, maybe I am wrong, I think you are looking for something like this:

Summarizing: yes, i think you can do what you need but we'll need more details.

Regards,

Jaime.

[peterderrington]

The image on the left is one of the types of image I'm trying to create but I just cant think how to do it - I'm blaming this cold! 🐵

[jaibau1993]

Hi Peter!

To build that chart you need to create a peculiar expression. As you may have noticed, the expression contains two calculations:

1. A label: yes or no (given some conditions)
2. A number: what specifies the heigh for each month

This can be done using "Dual" function. It takes two arguments: a text and a number. Thus, your expression should look like

Dual(
     //Yes/No label:
     if(Count({<[Session(1=AM 2=PM 3=Eve)]={'1'}, [Sent for Time] = {">9:00"}>} [Sent for Time]), 'Yes', 'No')
,
     //The numeric value (I'm guessing it)
     Count({<[Session(1=AM 2=PM 3=Eve)]={'1'}, [Sent for Time] = {">9:00"}>} [Sent for Time])
)

Do not forget to check the "Values on Data Points" checkbox in the expression tab!

Regards,

Jaime.

[peterderrington]

Hi Jamie, 

Thank you for all your time, I'm struggling to get it to show anything like your diagram. 

I've set it as a Pie Chart but there's no dimension (I know sometimes with Pie Charts I've not needed a dimension before but I'm not sure if that fits with this).

I've slightly altered the script (just because we had the less than/greater than symbol the wrong way round however the chart just shows:

I tried changing it to a straight chart just so I could see what count it was showing but it just shows "Yes"

The current expression is: 

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

I know that individually the expressions work, feeling lost - sorry.

[jaibau1993]

Hi Peter!

Just forget what I said, I misunderstood you again!

You need to create a 'calculated' dimension from the 'simple' dimension [Sent for Time]. For each value of [Sent for Time] :

• if [Session(1=AM 2=PM 3=Eve)] = 1 and >9:00 then you have to label it as 'Yes'
• if [Session(1=AM 2=PM 3=Eve)] = 1 and <=9:00 then you have to label it as 'No'
• if [Session(1=AM 2=PM 3=Eve)] <> 1 you want to discard it (is this right?)

You can do it using AGGR in a calculated dimension. Just check the attached file and ispect how I built the figures below!

Let me know any doubt!

Regards,

Jaime.