7 Replies Latest reply on Aug 23, 2018 11:15 AM by Michael Hesser

# Finding the earliest date when 70% of a population is reached

Hi Folks,

Hope someone could help me out on the following.

I would like to find the earliest moment when 70% of a population is reached based on contract end date for each  BAN....I can't get my head around it on a short notice;)

The outcome should be something like this

Find attached my file in version 10.4

Regards,

Norbert

• ###### 1. Re: Finding the earliest date when 70% of a population is reached

Hi Norbert;

I'd love to help, but can you give me a little more insight?

The way I see it, you have a list of dates, which I'm calling Report Dates.

The MINIMUM date, by ban, represents the Contract Start Date.

The MAXIMUM date, by ban, represents the Contract End Date.

You are counting each Report Date and trying to find the first one (per band) whose Index is >= (Count(Index)*.7)

Is this correct?

What I'm unclear about is in your outcome you show {a, b, c} when the sample bans are {b,c,d}.

Also, some entries have more than one entry per day. Do these count as unique instances, or are they one?

Thank you!

--Michael

• ###### 2. Re: Finding the earliest date when 70% of a population is reached

Here's what I came up with-- I hope it meets your criteria!

Index

Index()

Index should restart at each ban.

Reports (this is just a count of dates you have ban)

{fixed [ban]:(countd(ctn))}

ceiling({fixed [ban]:SUM([Reports])*.7})

I put the Alert Check in filters and assigned it to TRUE

My display looks like this, but you can hide the columns you don't need.

Hope this works! --Michael

1 of 1 people found this helpful
• ###### 3. Re: Finding the earliest date when 70% of a population is reached

Hi Michael,

Upfront thanks for the reaching out. I think your directions are correct but not able to replicate. Where does the [Pop] come from or could you share a workbook with the provided direction?

Have a nice day

Regards,

Norbert

• ###### 4. Re: Finding the earliest date when 70% of a population is reached

Ooops-- I renamed one of my calcs. [Pop] is actually Alert Index, so you'll need to modify Alert Check calc, too.

I've edited it above, as well as attaching a .TWBX. The modification is:

2 of 2 people found this helpful
• ###### 5. Re: Finding the earliest date when 70% of a population is reached

Hi Michael,

No probs, I figured it out and works perfect:)

Thanks a TON for the "HINT". Appreciated

Regards,

Norbert

• ###### 6. Re: Finding the earliest date when 70% of a population is reached

Hi Michael,

We would like to take it a step further;) I defined a overview sheet based on contract end date and for each contract end month give de amount of ban's.

Because we are using index() we need the lowest level of detail also in this view. Would a be a "workaround" without using index()?

At the same time we would like to sort the ban's based on the amount of CTN

Hope to hear from you

Regards,

Norbert

• ###### 7. Re: Finding the earliest date when 70% of a population is reached

Hello Norbert;

Glad I could be of help!

Could you provide me with a rough approximation of what you expect the detail to look like? I'm afraid I'm not sure how to interpret your diagram   Maybe I need a little caffeine?

The amt of CTN is represented by either the calculation [Reports] (my name), or your version [1. Count CTN per BAN]

{fixed [ban]:(countd(ctn))}

• Place this is on the Rows (if it's not there already)
• Convert it to Discrete (if it is not already)
• Move it to before [ban] and sort
• Hide the value if you don't want to see it

This should sort your viz by "count of CNT."

Is something like this closer to what you're getting at:

• Each CTN is represented, by month
• Each ban may appear in each month more than once, based on your CTN id field
• Bans are sorted based upon which has the greatest total number of CTN.
• I've highlighted the CNT that represents the 70% achievement (as in the original question)
• Just saw I misspelled "CTN"-- to be honest, I'm not sure what this stands for

Let me know if I'm way off base here