Using an Advanced Function based on existing column advanced function
Answered
Hi,
We have a report where we're getting the count distinct plus the weighted average values. We want to get the Delta value between the 3 month period (example on column F).
Because the column is already using an advanced function we can't use the results to calculate using another advanced function or, use two advanced functions in the same column.
In the example below, we have columns A, B & C as output from the database.Column D is the count distinct plus weighted average of column C.
How do we get to column F using the advanced function "Delta From Last N" please.
Kind regards,
Peter
Hi Peter,
Thanks for your question.
I believe this one is quite straightforward, however, I'm not sure how far you've made it within Yellowfin.
To get the Delta of Last N for defects, you simply need to:
The resulting output should be what you are after.
Kind regards,
Simon
Hi Peter,
Apologies, I've just noticed I misinterpreted your question here.
Unfortunately, you are unable to apply more than one Advanced Function to a column. You are limited in applying just the 'Weighted Average' or the 'Delta from Last N' Advanced Functions.
The best way to get around this if you can is to create a 'Weighted Average' column in the View or the Data Source using SQL, and then applying the Delta Advanced Function to the column. This would also improve performance as the Calculation isn't using JVM Memory.
Let me know if you have any further questions on this. The SQL might be quite complex to create, depending on your requirements.
Kind regards,
Simon
Hi Simon,
Thanks for the quick reply.
We had found this function and tried a variety of selections, but it needs to be applied to the weighted moving average results (column D), which we can't see how to do. Also we don't want the percentage, just a simple subtraction.
These are the results using your suggestion and various options:
#1. This is count distinct aggregation with Delta For Last N = 3 (not using percentage) .I'm not sure where the displayed delta figure comes from as if it's added or subtracted, the answer is not in either of the rows to the left.
#2. Using your example settings
Without the percentage formatting:
In each of the above examples the values in the Delta column do not match my original example results.
Kind regards,
Peter
Hi Peter,
I'm not sure if you received the previous message as it looks as if it was sent at the same time as the one you sent me.
To recap, it is not possible to stack Advanced Functions within Yellowfin. I'm happy to raise this as an Idea post to be considered for an Enhancement Request.
The best way around this would be to create a Field within your Database for the 3M moving average using ETL functionality for example, or alternatively within your View as a Calculated Field using SQL. You would then be able to use this field to calculate the Delta accordingly. I'm not actually sure if it's possible to create a field like this in Yellowfin using SQL, I would have to consult with our Developers.
Otherwise, it could be possible to create a Custom Advanced Function for your requirements using instructions from here.
Kind regards,
Simon
Hi Simon,
It was weird but I noticed your reply above mine. We're working on a Pre-Defined function at the moment which could be OK, if not we'll go down the path of the Advanced Function.
Thanks for your assistance.
Kind regards,Peter
Hi Peter,
Not a problem, let me know how you go with either option and feel free to reach out if you have any further questions.
For the moment I will go ahead and close this question, however, feel free to respond here and re-open the ticket where I will get back to you shortly.
Kind regards,
Simon
Comments have been locked on this page!