New Topic

Community Topic Question Report Builder

Eric Hagfors

Yellowfin Support Team

Assigned

Category Report Builder

Product Version 7.1

Can you use filters in Calculated Fields?

Matthew Jones shared this question ago

Answered

Hi,

I have a pivot table showing forecast monthly fee income for each project we're working on over the current financial year. I've been asked if we can add two columns to this table:

1. To show the total fees for each project previous to the current fianancial year

2. To show the total fees for each project forecast for future financial years

The current pivot table has a date filter set to 'Current Financial Year', but users are free to alter this to different time periods to suit their reporting needs.

For this reason, I think I need to create a calculated field to populate the two new columns. In the calculated field, I would need to reference the current date filter setting.

Can you use filters in calculated fields to achieve something like: SUM ('Fees') WHERE 'Fee_Date' NOT IN 'Date_Filter_Value'

In the example above 'Date_Filter_Value' could equal [Current Financial Year] or [Last Quarter] or some other time period.

I can't see a way to reference filters in calculated fields. Is there a way to do this or perhaps some other way to achieve my goal?

The same question

Replies (7)

Eric Hagfors ●

Hi Matthew,

You may achive success creating your content by utilizing the Parameter or Calculated Filter classes within Yellowfin -

https://wiki.yellowfinbi.com/display/user80/Parameters

https://wiki.yellowfinbi.com/display/user80/Calculated+Filters

Is this the type of functionality you're looking for?

Thanks,

Eric

Reply URL

Matthew Jones ●

Hi Eric

I don't think parameters or calculated filters will help... I need to be able to access the filters when creating a calculated field. e.g. Sum(Fees) where Fee_Date < Filter_Date

Can you suggest how either parameters or calculated filters might help with this problem?

Thanks for your help so far.

Matthew

Reply URL

Larry Beasley ●

Hi Matthew,

There are two ways to do this.

1. You cannot sum in a freehand SQL calculated field but you can accomplish what your report users need by simply adding [total fees] to the report and aggregate it using sum. Next, add Fee_Date in the filter section. The Fee_Date filter should be "Not Between". It maybe listed as different from. You can also link to a filter if you want this to be automatic. For example, you can link to another date range and then choose the operator you want and offset by a defined period. In your case you could possibly offset by financial period.

2. Look into using parameters. If you set up date parameter(s) for start date and end date you can certainly do what you're asking for. Also you can use parameters in calculated fields. Think of parameters as a declared variable. They are nice when you want to calculate as-of/what-if calculations. I used them for an aging bucket report and it worked out perfectly.

I would have loved to provide you with screenshots or a little more in-depth advice but I no longer have access to YellowfinBI anymore. I work in a different department now. :(

Reply URL

Matthew Jones ●

Hi Larry,

Thanks for your advise.

1. I can't use your suggestion of filters because I'm already using filters to show the current financial years Fees. There is no way i can see to add another filter to show data outside of the current financial year. They would cancel each other out. That is why I'd like to be able to do this in a calculated field - to effectively apply a filter to just one column!

2. Parameters are OK, but they would have to be hard coded to default to this financial year. Next year, they would have to be changed to be hard coded to next year. I can't see a way to set the dynamic date to be start date = 'The previous May' and the end date to be = 'Next April'.

Still looking for a good solution to this one! :)

Reply URL

Matthew Jones ●

Hi Larry,

With a bit of further investigation it looks like you can set the date to pre defined periods on Parameters, so i'll keep investigating this route... :)

Thanks,

Matthew

Reply URL

Larry Beasley ●

Hi Matthew,

After your last comment, I re-read your requirements. I don't know how I missed that you need two different time periods. I recommend approaching this report with a Union "subquery". In the subquery you want all the same filters and you want to link to the master query's filters. The date filter you can either offset by a period. or let the user choose the period.

For example:

2eae958a92565199fc0740f1e4a54400

Your master query is Year, Rating, Booking Method

Your subquery could be 'Previous Period', Rating, Booking Method

'Previous Period' would display as a cross-tab column header. There is so much you could do here. I think that this may be your best option as there is nothing restrictive about this approach. I have used it time and again.

URL

Eric Hagfors ●

Hi Matthew,

Just wanted to check in to see how you were able to make out with this issue?

Thanks,

Eric

Reply URL

Eric Hagfors ●

Hi Matthew,

I'm going to go ahead and mark this as Answered due to inactivity. Feel welcome to re-open with a reply with further related inquiries.

Thanks,

Eric

Reply URL