It would easy for us if you could replicate this problem using some sample dataset (e.g. superstore dataset) or give the expected output format in excel file.
Thanks again for looking into my problem. Superstore dataset would not help me to replicate this issue.
I try again to explain my issue here.
I have data as :
Indicator Key Indicator ID IndicatorName FrequencyOfReporting 1 10007346 Crude rate of surgical site infections following a caesarean section procedure per 100 procedures End of month 2 10007350 Rate of central-line catheter associated blood stream infections in intensive care units End of financial quarter 3 10007351 Rate of healthcare-associated Staphylococcus aureus bacteraemia infections per 10,000 bed days End of financial quarter 4 10007356 Percentage of unplanned readmissions within 28 days following discharge after a birthing episode End of month 5 10007357 Percentage of unplanned readmissions within 28 days after discharge following birth End of month 6 10007358 Average gain in the Functional Independence Measure score per day of care for rehabilitation patients End of financial quarter
Now, if i want to count all the reported indicators in last 3 months (July -Sep), i will get the data 3 times more for Indicator keys 1,4,5. Because every month these are being reported.
So my issue is how to get the latest reported time only , so that for Indicator keys 1,4,5 i will get only Count which were last reported in Sep and ignore the old data.
In other words i need to find the Indicator Reporting Start date and End date dynamically so that it will pick the latest reported indicator only.
Hope it is more clear now.
I don't know if my solution would work for your case as i don't have any idea how your raw data looks like. Still, i have tried to replicate the scenario using superstore data, with an objective to get latest month sales value in a particular state and quarter. For example, for Alabama, in 2014Q4, this will pick sales for December instead of November or October.
Attaching file for reference. Hope this sparks some idea to solve your problem. Please close the thread if this helped you.
LatestMonthValues.twbx 1.2 MB