1 2 Previous Next 23 Replies Latest reply on Jul 10, 2018 1:11 AM by raoul.tller

# Combining level of detail expressions

Hi,

Is it possible to combine level of detail expressions?

Example (can not post workbook, because superstore does not have data on datetime level (every minute)):

I get the first balance for selected period using:

IF {INCLUDE:MIN([datetime])}=[datetime] THEN [balance] END

Then I have this LoD to get the Lowest value over time. I am looking at Lowest Value in Months for the report I am working on (so lowest balance in that month was on this datetime moment).

MIN({INCLUDE [Segment],DATETRUNC('minute', [datetime]): SUM([balance])})

Now I want to have a LoD or calculation that gives me the First Balance of the Lowest Balance day, if that makes sense?

• ###### 1. Re: Combining level of detail expressions

Hi Raoul.

You definitely can! A very useful feature.

Do you have a workbook you can attach? If not I can create an example using Superstore momentarily (demonstrating something similar).

• ###### 2. Re: Combining level of detail expressions

Hi Bryce,

Nice! I have attached a workbook in the first post

• ###### 3. Re: Combining level of detail expressions

You'll notice all of my calculations use CF, so feel free to filter the dimensions/measures pane by searching on this.

First thing I did was make a standard {fixed [Order Date]: SUM([Sales])} to display the total sales (in this case, it rolls up by month).

Then I wanted to find the worst performing day in the month (this is going under the assumption that every day had sales, mind you, as we can't take into account dates that don't exist in the data):

To demonstrate the nested LOD, here's the returning of the worst date of the month:

I also was able to identify the category with lowest sales, but again, this only looks for categories that actually had sales on the date. In this case both days only sold Office Supplies, so I didn't feel it was worth returning.

Hopefully this can at least help!

To get to what you're after in original post, could try something like this:

CF_BalanceByDate:

{fixed DATE([DateTime]): SUM([Balance])}

CF_BalanceByDate_MonthMin:

{fixed DATETRUNC("month", [DateTime]): MIN([CF_BalanceByDate])}

CF_BalanceByDate_MonthMinDateTime:

{fixed DATETRUNC("month", [DateTime]): MIN(IF [CF_BalanceByDate] = [CF_BalanceByDate_MonthMin] THEN [DateTime] END)}

CF_MonthMinFirstDateTimeBalance:

{fixed DATETRUNC("month", [DateTime]): SUM(IF [DateTime]=[CF_BalanceByDate_MonthMinDateTime] THEN [Balance] END)}

1 of 1 people found this helpful
• ###### 4. Re: Combining level of detail expressions

Updated reply to include logic for what was in your original post.

• ###### 5. Re: Combining level of detail expressions

Hi Bryce,

I have been trying a bit more today and I am getting close to the number I need.

My current calculations:

Raoul_CF_BalanceByDate

[balance]

(just returns me the balance, I dont need to sum or anything, get a balance every minute)

Raoul_CF_BalanceByDate_MonthMin

{fixed DATETRUNC("month", [datetime]),[code]: MIN([Raoul_CF_BalanceByDate])}

(which gives me the month min per account (correct number))

Raoul_CF_BalanceByDate_MonthMinDateTime

{fixed DATETRUNC("month", [datetime]),[code]: MIN(IF [Raoul_CF_BalanceByDate] = [Raoul_CF_BalanceByDate_MonthMin] THEN DATE([datetime]) END)}

(which returns me the date of that min per account moment)

Now I want to use this Date to get the First Balance of that day per your suggestion:

Raoul_CF_MonthMinFirstDateTimeBalance

{fixed DATETRUNC("month", [datetime]),[code]: SUM(IF DATE([datetime])=DATE([Raoul_CF_BalanceByDate_MonthMinDateTime]) THEN

[Latest Balance]

//[First Balance]

//[balance]

END)}

For some reason this works with my Latest Balance calculation (it returns me 1.056.659) but not with my First Balance calculation (it returns me nothing, I would expect 26.926.195)?

Latest balance:

IF {INCLUDE:MAX([datetime])}=[datetime] THEN [balance] END

First balance:

IF {INCLUDE:MIN([datetime])}=[datetime] THEN [balance] END

Is there another way to return that 26.926.196 number?

• ###### 6. Re: Combining level of detail expressions

You were close! You were just missing the step. Thankfully it was pretty quick for me to find.

Raoul_CF_BalanceByDate_MonthMinDateTime was returning just a Date rather than a DateTime, so that made it jump out.

I renamed this to Raoul_CF_BalanceByDate_MonthMinDate. I then used this to create Raoul_CF_BalanceByDate_MonthMinDateTime.

This finds the first timestamp on the day. This was then used to compare to DateTime to find first balance:

One last caveat is to ensure you do one of the follower:

• keep Code in the LOD expressions OR
• right click Code and "Add to Context"

I suppose it depends on your use case. Attached final result here.

1 of 1 people found this helpful
• ###### 7. Re: Combining level of detail expressions

Hi Bryce,

Thanks again, one thing I have noticed that

Raoul_CF_BalanceByDate_MonthMin

{fixed DATETRUNC("month", [datetime]),[code]: MIN([Raoul_CF_BalanceByDate])}

Is not always returning the correct value in my full data set, for example it returns me -137.615.977 instead of -135.184.414 (I have sorted all data for the specific month and found that last value to be the lowest (minute based data). Also the value -137.615.977 is not even present if I look at the data for a code sorted ascending (all minutes and days).

With my other calculation:

MIN({INCLUDE [code],DATETRUNC('minute', [datetime]): SUM([balance])})

It does return me -135.184.414

So I want to use this calculation for the MonthMin but Tableau tells me:

Raoul_CF_BalanceByDate_MonthMinDateTime --> The field Raoul_CF_BalanceByDate_MonthMinDate is invalid

When I open that field:

Cannot mix aggregate and non-aggregate functions with this function

Cannot mix aggregate and non-aggregate comparisons or results  in 'IF' expressions

Is there a way to use  MIN({INCLUDE [code],DATETRUNC('minute', [datetime]): SUM([balance])}) or get this result without the sum?

I have tried changing DATETRUNC month to DATETRUNC minute but that gives me a different result again

• ###### 8. Re: Combining level of detail expressions

Sorry Raoul, I'm a bit confused. What do you mean: in my full data set? Just looking at different/more months and timestamps?

Also, one thing I wanted to point out: your function Raoul_CF_BalanceByDate_MonthMin references your other field Raoul_CF_BalanceByDate which is just [balance]. With this, it simply finds the row that is the lowest balance. Is this what's desired? I was previously summing the balances throughout the day - now I'm unsure this is the desired behaviour! Is [Balance] simply a running export of the "balance" (profits - losses)?

To address issue above (although please consider what I just wrote first!), you can likely wrap that all within {}.

But perhaps I need to readdress the overall goal. Again, previously we were summing as we were looking at lowest daily Sales in a given month. Might be different than what you're doing here.

• ###### 9. Re: Combining level of detail expressions

Hi Bryce,

Sorry for the confusion, the first post did not include datetime data which is what I am working with, should have started with that not with the superstore.

My full data set indeed includes data for all months/days/hours/minutes of 2018!

Every minute there is a balance and I always have to look at the most recent on so not summing any of it.

Balance is like the balance of your bank account with inflow and outflow of money.

What I am looking for is the Lowest Balance in a month.

Then when we have this Balance and specific date, I need to find the First balance of that specific date (so the MIN in datetime).

To find the lowest Balance in a month I am currently using the following measure:

Min Balance per Code

MIN({INCLUDE [code],DATETRUNC('minute', [datetime]): SUM([balance])})

This returns me the numbers that I expect.

I have tried wrapping this calculation like you suggested, it then will return me the same number for both april and may. (see attached workbook, bryce sheet).

If I check balances I see that I would expect:

April:

Lowest Balance is 5.261 on 29-4-2018 15:27:00

First Balance of 29-4-2018 is at 11:27 6.921

May

Lowest Balance is -96.000 on 30-5-2018 15:26:00

First Balance of 30-5-2018 is at 11:26 3.201

Raoul_CF_BalanceByDate_MonthMinDateTime (copy) is showing those numbers but just for May 2018, April stays empty for some reason?

Also when you show all codes (monitoring metrics sheet) it is empty.

I hope its clear what I am trying to achieve, thanks again!

• ###### 10. Re: Combining level of detail expressions

Well some more try & error, in my Full data set this calculation for MonthMin:

{MIN({INCLUDE [Code],DATETRUNC('minute', [datetime]): SUM([balance])})}

Returns the same random value for every Code, I assume it is missing something to be able to calculate it row based per code?

Any suggestions Bryce?

Bryce Larsen

• ###### 11. Re: Combining level of detail expressions

I just made a fake dataset consisting of timestamps and random numbers between 0-100. Here's the formula I made:

Here you can see the result:

Far right is the date with the lowest value. Column to the left is the first balance on that date. On 5/16 it's the same as the lowest of the month, but on 6/11 it's appropriately returning 72 as 5 (at noon) was the lowest value of the month.

Hopefully this helps. I think not using Include in this case might be beneficial.

Bryce

• ###### 12. Re: Combining level of detail expressions

Very odd, in my workbook it gives me the same result for every code (same as with the other one), see attached.

@

Test calculation:

{fixed DATETRUNC("month", [datetime]): MIN(IF [datetime] =

{fixed DATETRUNC("month", [datetime]): MIN(IF DATE([datetime]) =

{fixed DATETRUNC("month", [datetime]): MIN(IF [balance] =

{fixed DATETRUNC("month", [datetime]): MIN([balance]) }

THEN DATE([datetime]) END) }

THEN [datetime] END) }

THEN [balance] END) }

• ###### 13. Re: Combining level of detail expressions

Ah yes. Forgot about that. Add [Code] to the Level of Detail.

So always {fixed [Code], DATETRUNC("month", [datetime]): ...}

• ###### 14. Re: Combining level of detail expressions

That makes sense actually.

It is almost working, if I sort DIO by balance amount I can see that:

30-5-2018 15:26:00 was the lowest balance for May = -96000

29-4-2018 15:25:00 was the lowest balance for April  = 1337

For may -96000 the first available balance that day was indeed 3.201 which is what Test above displays.

But if I look at april

The lowest balance as 1.337 at  15:25 on 29-4, so I would expect Test to return the first available balance that day which was 8:27 --> 5000 not 4.915 (this balance does not even exist anywhere so I dont know where is comes from?)

1 2 Previous Next