topic Re: Count first sorted value based on additional selection in QlikView

Count first sorted value based on additional selection

peterderrington — Fri, 29 Nov 2019 12:38:15 GMT

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.

Re: Count first sorted value based on additional selection

jaibau1993 — Fri, 29 Nov 2019 13:50:33 GMT

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.

Re: Count first sorted value based on additional selection

peterderrington — Fri, 29 Nov 2019 14:38:56 GMT

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.

Re: Count first sorted value based on additional selection

peterderrington — Fri, 29 Nov 2019 15:05:37 GMT

A version of the app.

Thanks.

Re: Count first sorted value based on additional selection

jaibau1993 — Mon, 02 Dec 2019 07:52:44 GMT

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.

Re: Count first sorted value based on additional selection

peterderrington — Wed, 04 Dec 2019 20:42:38 GMT

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.

Re: Count first sorted value based on additional selection

jaibau1993 — Thu, 05 Dec 2019 07:32:02 GMT

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.

Re: Count first sorted value based on additional selection

peterderrington — Thu, 05 Dec 2019 09:22:51 GMT

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!!

Re: Count first sorted value based on additional selection

peterderrington — Thu, 05 Dec 2019 09:33:28 GMT

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'+ ?

Re: Count first sorted value based on additional selection

jaibau1993 — Thu, 05 Dec 2019 09:40:49 GMT

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.

Re: Count first sorted value based on additional selection

peterderrington — Thu, 05 Dec 2019 19:56:11 GMT

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

Re: Count first sorted value based on additional selection

peterderrington — Thu, 05 Dec 2019 21:30:28 GMT

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?

Re: Count first sorted value based on additional selection

jaibau1993 — Mon, 09 Dec 2019 07:55:01 GMT

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.

Re: Count first sorted value based on additional selection

peterderrington — Mon, 09 Dec 2019 09:20:59 GMT

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! 🐵

Re: Count first sorted value based on additional selection

jaibau1993 — Mon, 09 Dec 2019 11:05:36 GMT

Hi Peter!

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

A label: yes or no (given some conditions)
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.

Re: Count first sorted value based on additional selection

peterderrington — Mon, 09 Dec 2019 15:13:45 GMT

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.

Re: Count first sorted value based on additional selection

jaibau1993 — Mon, 09 Dec 2019 15:36:45 GMT

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.