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

    Combining level of detail expressions

    raoul.tller

      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
          Bryce Larsen

          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
            raoul.tller

            Hi Bryce,

             

            Nice! I have attached a workbook in the first post

            • 3. Re: Combining level of detail expressions
              Bryce Larsen

              Hi raoul.tller

              Please see attached.

               

              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
                Bryce Larsen

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

                • 5. Re: Combining level of detail expressions
                  raoul.tller

                  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
                    Bryce Larsen

                    Hi raoul.tller

                    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
                      raoul.tller

                      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
                        Bryce Larsen

                        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
                          raoul.tller

                          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).

                           

                          brc.png

                          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
                            raoul.tller

                            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?

                            fulldata.png

                             

                             

                            Any suggestions Bryce?

                            Bryce Larsen

                            • 11. Re: Combining level of detail expressions
                              Bryce Larsen

                              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
                                raoul.tller

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

                                test.png@

                                 

                                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
                                  Bryce Larsen

                                  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
                                    raoul.tller

                                    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

                                    LOD_sort.png

                                     

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

                                     

                                    lod_may.png

                                     

                                    But if I look at april

                                    lod_april.png

                                     

                                    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