Does the attached work? I created a calculated field to show the previous value and another to calculate the % difference. I though this would be possible using the in-built Percent Difference but it wouldn't restart for each Show by default hence I created it in 2 calc fields instead.
Difference.twbx 29.0 KB
Thank you for your superquick reply! It does seem to work, but it’s not exactly what I am looking for.
I attached a new file with a fake ‘report’of how I will use it/how I want it to show up in a Tabeal Sheet. Some comments:
- In a report I want to show a list of shows that aired in the last -for example- 7 days. This will be a list of about 50 different shows with their latest episodes shown. For all these latest episodes, I want to show the peformance compared to the episode before BUT those episodes wont show up in the final report..
- All columns will be sorted on the "On Air"column, from newest to oldest
- This blue column (Ep) is just to clarify my story, but is NOT available in my data and will NOT be shown in the final report.
- I hope you (or someone else) will find a solution J!
That's far more complicated...what you actually want to do is calculate the % difference but then filter out the show which you're using for the calculation.
This wouldn't work with standard calculations as filtering out the values needed to calculate the difference would mean the calculation fails as the data is no longer available to calculate.
A workaround is to use a Table Calculation as a filter. These filters happen after the view has been calculated - meaning your % difference remains calculated but not displayed. I've converted the date field to a table calculation and applied that to the attached.
Note the dates or episode numbers supplied look a bit dodgy - the latest episode in number is the earliest date aired.
Difference.twbx 27.0 KB
Thanks a lot for the explanation and the file you posted! I never heard of table calculation before. It works perfect now.. But when I add other dimensions to my sheet, it starts interfering with the Table Calculations.
If I add Airing Time of a show, and Channel name where the show is airing, the "PreviousValue" starts to disappear.
When I look at the Table Calculation of "PreviousValue" the discription says "Results are computed along Date (sorted descending by Count of Date) for each Channel, Show, Starttime, GenreType."
Could you explain why this is happening?
Yes, to get the calculation to reset for every show I had to go into the settings of the table calculation and tell it what to use for the calculation. That's not a topic to get into here, there are many different bloggers that have written about that, I think Jonathan Drummey has written extensively about this and I'm sure will happily point you in the right direction on his blog.
As a pointer you need to Edit the table calculation (click on the arrow on the pill):
Select Compute using Advanced to see the below. Drag your new fields into Addressing if required:
It's likely a lot of trial and error will be required here as this is difficult to understand.