5 Replies Latest reply on Aug 18, 2016 8:13 AM by Joshua Milligan

# Potential level of detail calculation

I have a calculated field question - maybe involving level of detail calculations?

I'd like to work out when each account in the table below starts spending in shop B. So, the MIN(Date) for when shop B starts seeing some revenue.

DateAccountTypeRevenue
01/01/2016Company 1Shop A\$0
01/01/2016Company 1Shop B\$1000
02/01/2016Company 1Shop A\$4000
02/01/2016Company 1Shop B\$2000
01/01/2016Company 2Shop A\$500
01/01/2016Company 2Shop B\$0
02/01/2016Company 2Shop A\$400
02/01/2016Company 2Shop B\$800

e.g. based on the above I should end up with:

AccountCalculated field result
Company 101/01/2016
Company 202/01/2016

What kind of calculation(s) should I use?

• ###### 1. Re: Potential level of detail calculation

You can do something like this maybe:

Then drag 'Type' to filters and select 'Shop B' to only display its data.

• ###### 2. Re: Potential level of detail calculation

I'm not sure the level of detail calculation is needed.  If you are going to filter to Shop B, why not just use MIN(IF Revenue > 0 THEN Date END)? Or am I missing something?

-Joshua

• ###### 3. Re: Potential level of detail calculation

Yeah I sometimes over-complicate things That's right, no level of detail needed!

• ###### 4. Re: Potential level of detail calculation

Many thanks for your replies! I was actually not planning to filter by shop B. Is there a solution you can think of that doesn't involve this?

• ###### 5. Re: Potential level of detail calculation

Andreas,

You could add that as part of the condition

MIN(IF Revenue > 0 AND Type == 'Shop B' THEN Date END)

Any reason not to filter?  The calculation will lock it to Shop B and will be a little less efficient (because all data will be returned and evaluated instead of filtering to only Shop B).  Providing a filter would give you flexibility to dynamically change the Shop as desired.

Best Regards,

Joshua

Pooja Gandhi, I have the same over-complicating tendencies

2 of 2 people found this helpful