Is there a function that gives me the total number of days for a selected period

Rohan Hazarika shared this question 2 months ago
Answered

Is there a function that gives me the total number of days for a selected period?


On my report, the user is going to select a date range. I have a numerical chart, that should show the average value over the date range selected. However, i cant use the inbuilt average function because my dataset has multiple records for a single day (some with 0 or nulls). My average is not correct because of these records (and these records cannot be removed). Is there a way where i can take the sum and divide it by the number of days?

Comments (5)

photo
1

Hi Rohan,

Thanks for contacting Yellowfin!

Would you mind sending over a screenshot of what you've built so far so I can take a look at the data and layout?

My assumption is that an average that includes multiple records from the same day that have non-zero values would be correct. Yellowfin does allow you to hide zero and null values, rather than remove them entirely. Is that something you can do?

You'd probably want to create a separate column in the table that has the values minus the 0s and nulls and then reference that with a second chart that simply shows the average as a single number, and drop it next to the first chart in the design phase of building your report.

Kind regards,

Chris

photo
1

/063c9cd0119a0ad6bda32b1be8889dc9


This is what my data set looks like, i need to show the average volume between 01-Jan and 04-Jan which is equal to Sum(volume)/No of days.

the dataset is going to contain years worth of data, when the user selects the desired date range in the filter, i need to build a chart to show the average volumes over the selected period, average volume for planned, and average volume for unplanned. How do i calculate the number of days?


Hopefully, the above scenario helps you understand the issue

photo
1

Hi Rohan,

I'm putting this one past my team, as I couldn't see a way to do it with a conventional calculated field, which won't let you apply a SUM calculation on to a COUNT DISTINCT, but I believe I will have a way forward for you shortly.

I will recommend that once you can get that value, doing average volumes for planned and unplanned should be simple, using the Set Analysis chart function.

Kind regards,

Chris

photo
1

Hi Rohan,

I was able to build something similar in Yellowfin, using some of the default Ski Team data.

Is this what you are looking for?

We're using set analysis to create the numerical charts that show you the average for planned and unplanned and then a date range filter to filter the overall report.

Let me know.

Kind regards,

Chris

photo
1

Hi Rohan,


I hope things are going well over there.


Just wanted to let you know I'll be closing this request due to inactivity. However, if you ever wanted to re-visit this or have anything else I can help you with, please let me know.


Regards,

Chris