Comparing assessment scores

Hi,

I have in different ways but not in the way you suggest. What I have so far is:

CASE WHEN score > 9 THEN MIN ( Date ) END

That will return the first date that had a score > 9, but I also need the score on that date. How would I get that score instead of the date? Thanks,

Travis

Actually, it almost has to be a subquery because the MIN or MAX will be limited to the line, the data isn't aggregated. For example:

Client A-------Date------Score

Client A-------Date------Score2

Client B-------Date------Score

etc.

Hi Travis,

If you need both the date and the score, then in the Report Builder you'd leave in your original calculation, but also add your 'Score' field.

Here's a similar example using our tutorial data - this is the Calculated Field:

Which looks like this in the Report Builder:

Since I'm basing my equation on "Invoiced Amount', I'll just bring in that field next and remove the SUM Aggregation:

Since we don't have intimate knowledge of your data set and reporting requirements, it's difficult to provide further details on this. Client Report Creation is something Consulting is better equipped to handle, as Support typically focuses on replicating potentially defective behavior. That said, if you have any other specific broad-level questions we'll try our best to answer them.

Hopefully this helps!

Regards,

Mike

Hi,

The calculated field then is just returning all the rows right? Is it really doing anything with the MIN statement? That was a flaw I realized in my calculation...

Also, I think I misunderstood the nature of the forum - is this really just for support with problematic behavior? I was thinking it was more of a community of users that could help each other out and share report ideas. Thanks,

Travis

Hi Travis,

In terms of the Community, the way you've understood it is indeed an ideal additional function of the Community, but while this is something we'd like to see more of, in practice, we don't currently see all too much Community input from non-Yellowfin agents, so we typically get users' issues either headed in the right direction or resolved, so you won't have to wait too long to work through your issues. We want to make it so you're in good hands regardless, haha.

As for your issue, please let me know if I'm misunderstanding, but I think your calculation is correct based on looking for this desired result:

It just requires the addition of the Score field itself into the report.

Using my example above, just adding an additional column and changing the titles to more easily visualize what's going on, this is what it looks like:

This seems to match your requirement as shown above of having more than one score per date.

As an additional note, you will indeed see different results if you were to remove the MIN aggregation from the Date field - I get over 700 additional rows when doing so. Please let me know if I'm missing something here.

Thanks,

Mike

Ah, ok makes sense.

Oh... sorry I think I wasn't clear enough. This part:

Client A-------Date------Score

Client A-------Date------Score2

Client B-------Date------Score

Is actually the raw data. What is need is the following output:

Client A----------First Score Date----------First Score-------Last Score Date------Last Score-------Diff

Ex.

Client 12345----------1/1/19-----------16----------6/1/19------------12----------- -4

Client 67890----------2/4/19-----------19----------7/1/19------------15----------- -4

So each client is only listed once, and the first score has to be greater than 9. If a client had 10 assessments, and the 1st was an 8 score and the 2nd was a 12 score, we would skip the 8 and use the date/score from the 12 for the purposes of the first score, and then the 10th assessment would be the last score, regardless of the score.

As you can see it's a bit much... thanks for any input you can provide.

Travis

Hi Travis,

Thanks for clarifying. I don't have a data set similar to yours to play with, but I believe if you configure two append sub-queries, bringing in your Date Field into each, one set to Minimum:

And the other to Max, you should get the result you're looking for. I have too many repeat values in my data set to get something similar to your particular result though, but as you alluded to earlier, I do believe you'll need to utilize sub-queries and Min./Max. aggregations. It's just difficult to be much more specific than that without having your data to play with.

Regards,

Mike

Ah, ok thanks. This is what I had ended up with, but it has it's limitations:

You can't perform a calculation on a field that's in another subquery unless you use the advanced function. Which I guess is fine, but even that has its limits. The other thing is that you can't group the advanced function result, either by using the grouping drag and drop function or doing it manually via a calculation. Because of this, I can't make a chart from the data - I have to export it to excel, group the values via helper column and then create the chart there. This makes it so I can't use it on a dashboard in Yellowfin. Do you have any suggestions?

Travis

Hi Travis,

Thanks for your response. You are correct about these limitations. In terms of ways around it, I suspect there would be one or more way is to do so - this likely would ultimately come down to data prep. One potential thought, though again, I can't say with much accuracy not knowing your data, but you may be able to avoid having to use Grouping or referencing fields used in multiple sub-queries within Calculated Fields if you change the data before it even hits the report, which can be accomplished by utilizing Data Transformation's. You can read more about these on its corresponding Wiki entry here.

Unfortunately, Support is not in an ideal position to provide high-level recommendations, a Consultant would be better-equipped to take the time to walk you through data prep and report setup in order to get your desired results in the most efficient manner, but if you have any other questions should any specific product-related issues arise, we can do our best to address them.

Regards,

Mike

Ok, thanks for the help.

Travis

Hi Travis,

You're welcome! These points considered I'll go ahead and close this case out for now then, but the ticket is still searchable and further input can still be provided by other users. Also, if you have any other questions on this or anything else, please don't hesitate to reach back out for further assistance.

Regards,

Mike