How to format a field depending on the value of another field?

Ingo Klose shared this problem 2 years ago
Resolved

Hi,


I have a problem, I want to format one field depending on the value of another field and I can't find any way to do it. I though I can use a custom function or an custom advanced function, but then I would have to return Strings, which probably won't play nice with aggregations on the report. I also can't use a custom formatter, because according to the documentatione it does not allow me to access the values of a different field.


In the current use case I have a column with a value and a second column that tells me what kind of value it is. e.g. a € amount or a % amount. Depending on the type I would liket to format to <value> % or <value> €. I might even have to change the percision.


But I need to be able to aggregate the values of the same type when I add another dimension onto the report.


Any ideas on how to achieve this? Am I missing something?


Best regards,

Ingo

Comments (5)

photo
1

Hi Ingo,


I think that the only way to do this will be to write a custom advanced function (you can use the Python or R plugin to make this process easier).


Applying a custom format based on another column will be easy, but as you note, will need to return a string.


I am curious as to how dynamic your aggregations will be. Typically advanced functions should be your last stop (the report is in its final state, and no aggregation or calculations applied after the fact). If you are programming your own function you should be able to account for whatever small adjustments are made within the data, and return a field appropriate to this, at which point a string should meet your needs entirely.


Could you elaborate a bit on what sort of dimensions/aggregations you intend to apply after the report has been created?


Regards,

Nathan

photo
1

Hi Nathan,


Thank you for the input.


Basically we have KPIs and and a company hierarchy. Usually we show the value of a KPI per company Level 1 and then want to sum up the values for the whole company level 1 as a total in the report. I added some screenshots that show the current state and what we would like to do.


What we have now:


e7d3d833007c833fd01d55d2bdd6b937


And this is what it should look like...CR is defined as a % KPI where as Clicks is defined as a Integer KPI:


20a4e69f9f14a95044d2f6ed30c6ff9e


But the table totals should still wor, despite the difference in formatting.


By the way, could you provide some information about the Python an R Plugins? I could not find any information on them.


Best regards,

Ingo

photo
1

Hi Ingo,

Thanks for the screenshots. From what you have shown me I do not think this will be possible, mostly because you have row totals, which inherently require a numeric value. While you can dynamically adjust a column's formatting, the result will need to be a string. Any data formatting applied after the calculations such as prefixes are pulled from the configuration database, so there is no way for us to adjust them dynamically.

The Python and R plugins are unofficial add-ins to Yellowfin (created outside of the dev team) that allow you to employ Python and R scripts on top of your data-set, and return a result in replacement of a single row. These are applied as advanced functions that accept up to 5 columns as variables.

Both of these are quirky and have limitations which have prevented them from being officially adopted, but they can be powerful in the right use cases. The Python plugin employs Jython to run your python script, which limits you from using C based python libraries such as numpy and pandas . The R plugin is run through the Renjin engine, which works well sometimes but throws odd errors and doesn't work. (works poorly in more recent versions)

Here is a link to the git-hub page containing the necessary jar files for the R plugin: https://github.com/chancecoble/yellowfin-r-roll

The python script was created more recently and behaves better but does not have any documentation at the moment. For this you will need the Jython standalone jar (too big to attach) that can be downloaded here: http://www.jython.org/downloads.html . As well as the primary plugin jar which I have attached.

(Just place these jars in your appserver/webapps/root/WEB-INF/lib folder)

The screenshot I have attached shows the python plugin performing the equivalent of "running total" - "running total"

If you are interested in either please let me know and I can put a little documentation together.

Regards,

Nathan

photo
1

Hi Nathan,


I was afraid that would be your answer. ;-)


I'll be in touch, if we want to use the python Plugin. Although I doubt we put effort in it, if it does not solve the whole problem. But it is good to know, that these kind of plugins exist. Is there somwhere a list of other "secret" plugins that might be usefull?


Thanks again.


Best regards,

Ingo

photo
1

Hi Ingo,


Sorry for the delay on this, currently these are the only two addins of their kind, so the list is pretty short. However, with the introduction of JS charting, we are going to be introducing a "community content" section that will allow users to post functions that they have found useful, and things like this will go in there.


I am going to close this for now, but if you have any other questions please just respond and the ticket will be reopened.


Regards,

Nathan