Using an Advanced Function based on existing column advanced function

Peter Ruth shared this question 35 days ago
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.

/c4f0cd1847f3fde473d90de91bcff9b1

Kind regards,

Peter

Comments (6)

photo
1

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:

  1. Create a separate column using Defects and then apply a Count Distinct Aggregation
  2. Then apply the Advanced Function 'Delta for Last N', setting the previous row to 3 and toggling percentage on
    /71f8cdee60011391428702dfaa6ae0f3
  3. Once you've done this, change the formatting to 'Percentage'
    /400a0e7187b6b98b923538779db37216

The resulting output should be what you are after.


/e51bda117419d2700deb906c2a603b8f


Kind regards,

Simon

photo
1

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

photo
1

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.

/5d68c9a13474d3281acb8f0247f7e25f

#2. Using your example settings

/c8f4ba0a609da9b991833ce8314f4be8


Without the percentage formatting:/4e5eefbdd40ae17ada11c592f8481e91

In each of the above examples the values in the Delta column do not match my original example results.

Kind regards,

Peter

photo
1

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

photo
1

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

photo
1

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