I think I see the issue--your comparison withing v_calc is the problem because
" if ATTR([Month]) < [Max_Month] " is never going to equate to true. I've laid out all of your fields on the row shelf to try to demonstrate what's happening. You need to change the logic to:
" if ATTR([Month]) <= [Max_Month] " for the condition to work. I've done this within the v_calc UPDATE field in my workbook.
Alternatively, you could simply remove the "If" condition altogether (so that the field was just sum(col1)/sum(col2), plot it against the Month variable, and then filter then months that you'd like to see. I think this would give you the same result.
Let me know if this is helpful or correct. Thanks,
Thanks for the response. But I still didn't found the exact answer. Could you please look into this again.
1. I want to calculate the "sum(col1)/sum(col2)" for the last 6 months i.e. Max_Date and 5 months before that.
2. Also, the application you shared with me didn't had the the Max_Month as Fixed. It was changing for every month. I want that to be fixed then only I would be able to compare the rest of the months with the Max_Month.
1 of 1 people found this helpful
Ahh I see. I've made some modifications to the workbook--
1. I've made Max_Month fixed on the greatest of all the months. I changed the calculation to Window_Max rather than just Max(). You need to make sure to compute this along Month for it to work correctly.
2. To show the last 6 months of data, I've modified v_calc UPDATE so that the formula is simply Sum(col1)/Sum(col2). From there, I updated the v_Lat_Month field to operate as a boolean filter. The code is like this:
I think this gives you your desired result, but I'm not positive so please let me know if this is helpful or correct. Thanks,
Sorry for replying late. The answer was really helpful. I was actually posting in bits n pieces so as to get a clue and solve the problem. But unfortunately I am not able to solve my problem.
It would be really helpful if you could help me with the problem.
we need to create a chart(may be donut/ bullet punch or any other chart to show KPI) that displays data for the last six months i.e. if we have data from Jan 2014 to Dec 2014, the aggregated data for Dec 2014 to Jul 2014 should be displayed ( formula=sum(col1/col2) ). If now user selects Nov 2014 from the Quick filter then the aggregated data for Nov 2014 to Jun 2014 and so on.
Try this. I've created a parameter where you select the final month. So if you select December 2014, you will see July 2014- December 2014. If you select Nov 2014, you'll see June 2014 - Nov 2014. Note that if you select a month with less than 6 months before it, you'll only see those months instead of the full previous six months. So, if you select March 2014, you'll see January 2014 - March 2014. Is this what you're looking for? I've attached a workbook.
Thanks Ben, it worked. I was almost there but missed the use of Parameter. Thank you very much.
Glad I could help