3 Replies Latest reply on Sep 23, 2018 11:36 PM by Rahul Singh

# Calculations

Hi All,

I need your help to derive following calculations in Tableau.

I am counting Performance indicators, which have different frequency of reporting time for example End of month, End of Financial Year, End of Financial quarter.

I need to count the indicators based on their latest reporting time.

Currently, if i count all the indicators in last 3 months, some indicators values are getting tripled, because their frequency of reporting time is every month end. I only want to count indicators based on latest reporting.

Please help, how can i derive the dynamic start date and End date of Each indicator's reporting period

Thanks.

• ###### 1. Re: Calculations

Hi,

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.

Regards,

Rahul

• ###### 2. Re: Calculations

Hi Rahul,

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.

Thanks.

• ###### 3. Re: Calculations

Hi Praks,

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.

Regards,

Rahul