1 2 Previous Next 26 Replies Latest reply on Sep 24, 2018 10:03 AM by Fabian Aparisi Go to original post
• ###### 15. Re: Viewing Data YTD but With a Month Level Filter

I am very confused, but if you are talking about this is the issue.

You can edit color

If you are talking about "different color coding" for G.total, that's different story, but I still don't understand correctly what is your issue.

Thanks,

Shin

• ###### 16. Re: Viewing Data YTD but With a Month Level Filter

Oh wow... I totally did not see that. Well that solves the color issue.

The main issue is trying to do the month filtering in a calculation and not the sheet, which we're still working on. So I guess to maybe have me better explain it, which part are you confused about? For me, I'm looking for a formula that has the ability to do the monthly filtering inside of it instead of doing it in the sheet, so I do not have to have the months included in the sheet itself. I'm assuming this would be a LOD (Level of Detail) calc formula.

• ###### 17. Re: Viewing Data YTD but With a Month Level Filter

You mean this?

Thanks,

Shin

• ###### 18. Re: Viewing Data YTD but With a Month Level Filter

Sorry for the late response. Work got busy and then I forgot to reply.

I'm a little confused what the formulas are doing. Can you explain them to me? I'm looking at them and the results images you have and I'm not sure why it would be limiting the CURE_RATE values from April to July in both years.

• ###### 19. Re: Viewing Data YTD but With a Month Level Filter

It's limiting (filtering) the value(from data level, not only display) only more than parameter 0.95 0.96 what ever.

Shin

• ###### 20. Re: Viewing Data YTD but With a Month Level Filter

Ok, so it looks like it's working. So if I understand it correctly, the Fixed LOD formula in:

{fixed datetrunc('month',[SEC_ENTER_DATE]): min( if

{fixed datetrunc('month',[SEC_ENTER_DATE]):SUM([PURGE_STATEMENTS_CNT])/SUM([STATEMENTS_CNT])}

>=[Parameter 1] then

{fixed datetrunc('month',[SEC_ENTER_DATE]):SUM([PURGE_STATEMENTS_CNT])/SUM([STATEMENTS_CNT])}

end)}

is saying If the purge rate formula result is greater than the parameter that I created (where the 96% is located) than display the purge rate. Otherwise it will return a null result. The only thing I'm not fully understanding in the formula is the {fixed datetrunc('month',[SEC_ENTER_DATE]): min( at the beginning. Why is it set up as a MIN?

• ###### 21. Re: Viewing Data YTD but With a Month Level Filter

Tableau's formula requires the "some" aggregation expression at that position.

From the Formula, the value is already narrowed to only single value, then min. max. avg does not matter actually.

"Min" requires shorter finger stroke than other aggregation expressions.

Thanks,

Shin

.

• ###### 22. Re: Viewing Data YTD but With a Month Level Filter

Sorry for the double post, but something I realized, in your reply that I marked as correct, you have a filtered cure rate based on the cure rate being 95% or better and a purge rate where it's 96% or better. What I was actually looking for was a formula that gave a cure rate where the purge rate was for the month 96% or better. So it would be a hybrid of the two. I tried to use your purge rate formula and put the cure rate formula into the result but the totals (the subtotal and the grand total) are showing over 100% instead of the 96.1% that it shows in the example workbook. I understand what it's doing, but I don't understand WHY it's doing it. Basically what it's doing is doing a SUM of all of the cure rates for each month.

So I can use my purge rate filter that utilizes the parameter to only show cure rates at 96% or better aggregated at the YTD level and remove the month dimension, but if I try to put the purge rate parameter into the cure rate formula (the original one, not the one you created) the aggregation breaks.

Any thoughts?

For reference, the cure rate formula I created using your purge rate formula is:

{fixed datetrunc('month',[SEC_ENTER_DATE]): min( if

{fixed datetrunc('month',[SEC_ENTER_DATE]):SUM([PURGE_STATEMENTS_CNT])/SUM([STATEMENTS_CNT])}

>=[Parameter 1] then

{fixed datetrunc('month',[SEC_ENTER_DATE]):SUM([CURE_STATEMENTS_CNT])/SUM([STATEMENTS_CNT])}

end)}

• ###### 23. Re: Viewing Data YTD but With a Month Level Filter

Unfortunately I don't understand what you are seeing.

Please attach workbook which has errors and explain/illustrate what is wrong and what is the expected value.

Shin

• ###### 24. Re: Viewing Data YTD but With a Month Level Filter

It's using the same example workbook, but I added grouping that I didn't have before. But here's an illustration of what I'm looking for:

The CURE_RATE - Filtered is where I have the formula from above. The one where I took your purge rate LOD formula and changed the result to be cure rate instead of purge rate.

• ###### 25. Re: Viewing Data YTD but With a Month Level Filter

Your table structure is Completely different from the original and I lost your expectation.

Lost month completely from the table and now only showing year and add other measure.

I don't understand the logic to filter out the value with the relationships of "monthly data"

Please post new tread with clear logic explanatioins and problem statement.

Thanks

Shin

• ###### 26. Re: Viewing Data YTD but With a Month Level Filter

I don't understand what you mean. The table is exactly the same as what I was referencing this entire time. the only difference is instead of only showing the grand total I'm showing it at the group level and grand total. I even said that's what I was doing earlier in the thread (and showed an image of the entire dashboard) but that wasn't in the example workbook.

But since it sounds like you're done here, I'll open a new thread.

1 2 Previous Next