Once again I’ve been exploring the depths of what Yellowfin can do, you can read my previous Yellowfin blogs here. I thought I’d found a limit but after a bit of research with a lot of trial and error, I discovered that Yellowfin just likes things done in a certain order when calculating values across more than one dataset.
What was the problem?
We wanted to do a simple calculation of [Master Query].[Metric 1] – [Sub-query].[Metric 1]. When we tried to create this calculated field across a master query and a sub-query it would grey out the options for the other dataset depending on which one you chose first.
Why did this happen?
At it’s core Yellowfin uses SQL queries to return the data for your chart with a very clever interface on top that makes it simple. This means that to get the data you want for a report it generates the SQL needed on the fly to return the result you’re looking for.
The reason we had the issue was due to both the master query and sub-query having fields with the same name at the view level. We joined the data back to itself using an offset date so we could compare the value from the current month’s record to the previous month, this meant that the field names in the Master query and Sub-query were identical which in SQL terms meant ambiguous columns.
What was our solution?
The way around this is to create sub-queries before dragging any fields in to the columns and rows sections. This way Yellowfin knows that it’s dealing with two datasets and works out there are columns with the same name assigning them unique names (behind the scenes) to return the right values.
One last thing we did was to rename the fields when you drag them in so you can easily identify them when creating charts, easy to get confused later if you have two fields the same.
We did an upgrade between when we first had the issue and to when we worked out the answer. I’m pretty sure this didn’t make a difference but in case you still can’t get it to work, might be worth checking the version you’re running. We’re now on v7.1 Build 20150908.
Keep exploring! Daniel.