9 Replies Latest reply on Sep 27, 2018 9:10 AM by Fabian Aparisi

# Viewing Data at YTD Level but With a Month Level Filter Inside the Formula

Hello all,

I have a view that is trying to show YTD values, but I need it to be filtered by a value at the month level, and I need that filter to be in the formula, not in the filter sheet so it's not filtering every measure. Let me explain.

I have a sheet:

I currently have my month measure hidden. The reason for that is because I am trying to filter all Cure rate values where the month purge rate is only 96% or higher for that month. The problem, however is that because my purge rate filter is in the Sheet's filter area, it's also filtering out any SEC that were part of those months that have less than 96% purge rate.

So long story short, I'm looking for a formula that would calculate the cure rate, but only use the values if that month's purge rate is greater than or equal to the parameter I have set up. A visual example of what I'm trying to do is:

So the end result is something like:

I've attached an example workbook of what produced the images above.

• ###### 1. Re: Viewing Data at YTD Level but With a Month Level Filter Inside the Formula

HI Fabian,

Hope this helps.

Your judgement granularity(month) and aggregation  granularity(entire year as g.total) is different which makes calc setting complicated.

Thanks,

Shin

• ###### 2. Re: Viewing Data at YTD Level but With a Month Level Filter Inside the Formula

Hey Shin,

I appreciate your help with this, but it looks like there may be something wrong wih the end formula. The "grand Total" row is mimicking the same cure rate as the Special Cure Rate. I thought it may have been a coincidence but I moved the decimal place out further and they're the same numbers:

• ###### 3. Re: Viewing Data at YTD Level but With a Month Level Filter Inside the Formula

Try Change Min to Sum

Shin

• ###### 4. Re: Viewing Data at YTD Level but With a Month Level Filter Inside the Formula

Looks good, thank you .

• ###### 5. Re: Viewing Data at YTD Level but With a Month Level Filter Inside the Formula

Hey Shin,

How would I edit the formulas if I wanted to have it only be for 2018? I know I can just hide the 2017 part of the sheet, but I'm wondering if I can just have it in the formulas as well.

• ###### 6. Re: Viewing Data at YTD Level but With a Month Level Filter Inside the Formula

Not exactly sure but this?

Thanks,

Shin

1 of 1 people found this helpful
• ###### 7. Re: Viewing Data at YTD Level but With a Month Level Filter Inside the Formula

Ok, cool. Thank you

• ###### 8. Re: Viewing Data at YTD Level but With a Month Level Filter Inside the Formula

Could you mark "helped" if you don't mind.

Shin

• ###### 9. Re: Viewing Data at YTD Level but With a Month Level Filter Inside the Formula

done!