1 2 Previous Next 15 Replies Latest reply on Dec 8, 2016 7:11 AM by Walt Reed

    How to use an IF statement with aggregated data using the date as a condition

    Filipe Hemsworth

      Hi all, Im trying to display a calculated field using an IF statement, using the date field as the condition. The calculated field i'm trying to include is called 'Organic Order Revenue', this field is currently aggregated.

       

      My code is as follows:

      IF DATETRUNC('week',[Date]) = DATETRUNC('week',[Date Parameter]) then [Organic Order Value] END

      How would I go about modifying this code so that it would work with the aggregation? Is there any simple way of manipulating the date field such as calling it with a MAX() etc?

       

      Many thanks

        • 1. Re: How to use an IF statement with aggregated data using the date as a condition
          Matthew Risley

          as you suggested, you can try to wrap the datetruncs in Min, Max, or ATTR (I've personally stuck with using the ATTR function)

           

          What I've personally done in the past is: You could also create the view you need using Organic Order Value and then use a True/False filter and select the date using the date Parameter you've created.

           

          -So DATETRUNC('week',[Date]) = DATETRUNC('week',[Date Parameter])

           

          -and then filter on "True".

          • 2. Re: How to use an IF statement with aggregated data using the date as a condition
            Norbert Maijoor

            Hi Filipe,

             

            Go for

             

            IF attr(DATETRUNC('week',[date])) = attr(DATETRUNC('week',[date Parameter])) then attr([Organic Order Value]) END

             

            Let me know the outcome...

            • 3. Re: How to use an IF statement with aggregated data using the date as a condition
              Filipe Hemsworth

              Thanks for the response Norbert. Unfortunately as the 'Organic Order Revenue' field is already aggregated, it wont let me apply ATTR() to the field. Additionally it seems that by using ATTR() on the DATETRUNC functions seems to make Tableau unable to use the dates in the IF statement, as I figured out by testing on a non-aggregated field. Thanks!

              • 4. Re: How to use an IF statement with aggregated data using the date as a condition
                Filipe Hemsworth

                Thanks Matthew, this may be the way i'll have to go about. Will let you know how I get along!

                • 5. Re: How to use an IF statement with aggregated data using the date as a condition
                  Walt Reed

                  Hey Felipe,

                  Matthew and Norbert both had correct solutions--there's more than one way to skin a cat. The only modification I had to make to Norbert's formula was taking out the ATTR from date parameter and Organic Order Value:

                   

                  Let us know if this works.

                   

                  Walt

                  1 of 1 people found this helpful
                  • 6. Re: How to use an IF statement with aggregated data using the date as a condition
                    Norbert Maijoor

                    Hi Walt,

                     

                    Thanks for the "support";) Appreciated:) #skinacat

                    • 7. Re: How to use an IF statement with aggregated data using the date as a condition
                      Filipe Hemsworth

                      Walt, Your modification to Norbert's solution worked perfectly. Thank you ever so much!

                      • 8. Re: How to use an IF statement with aggregated data using the date as a condition
                        Filipe Hemsworth

                        Hi,

                         

                        I have come across another issue using the ATTR() method of aggregating the dates.

                         

                        Although using this method seems to give me the correct results, for some reason Tableau will only display the results if I have some sort of 'Date' dimension in the column/row. The 'Week Selection' field i'm using is simply a calculated field which is filtering the date to show 'This Week', 'Last Week', and 'This Week Last Year' using the following code:

                         

                         

                        IF DATETRUNC('week',[Date]) = DATETRUNC('week',[Date Parameter]) then

                        "This Week"

                         

                        ELSEIF DATETRUNC('week',[Date]) = DATEADD('week',-1,DATETRUNC('week',[Date Parameter])) then

                        "Last Week"

                         

                        ELSEIF DATETRUNC('week',[Date]) = DATETRUNC('week',DATEADD('year',-1,[Date Parameter])) then

                        "This Week Last Year"

                        END

                         

                         

                        Here are the results using the code you mentioned previously, Walt.

                        IF ATTR(DATETRUNC('week',[Date])) = DATETRUNC('week',[Date Parameter]) THEN

                        [Organic Order Value] END

                         

                        As can be seen the results are displayed correctly below:

                         

                        However removing the 'Week Selection' dimension breaks it!

                         

                        My end objective is to create a calculated field which works out the % difference between the fields 'Organic Revenue Last Year' and 'Organic Revenue This Week'. At the moment the I cannot get this calculation to work, and I suspect due to the fact the source fields are relying on some sort of date dimension to even display results?

                         

                        Many thanks.

                        • 9. Re: How to use an IF statement with aggregated data using the date as a condition
                          Walt Reed

                          Hey Felipe,

                          Do you want to show the three values: Revenue last year, revenue this week, and the % difference, or only the % difference?

                           

                          Walt

                          • 10. Re: How to use an IF statement with aggregated data using the date as a condition
                            Filipe Hemsworth

                            Hi Walt,

                             

                            I actually just want to create a bar chart to show how different KPIs have changed by % since the same period last year. So i'm creating an individual field for the value last week, then another for the value last year, and dividing by eachother to see the % difference.

                             

                            Here is one I prepared that is fully functioning (Code below). This one worked without any issues as the field being called is not an aggregated one, like the 'Organic Order Value' field which is.

                             

                            IF DATETRUNC('week',[Date]) = DATEADD('week',-1,DATETRUNC('week',[Date Parameter])) then

                            [Transactions] END

                             

                            Similarly to the example above, I have managed to create the data table without a problem, it just seems to be the part comparing one week to another which im struggling with due to the aggregation of 'Organic Order Value'.

                             

                            Thanks

                            • 11. Re: How to use an IF statement with aggregated data using the date as a condition
                              Walt Reed

                              Hey Felipe,

                              How is the Organic Order Value field calculated? Maybe it would be possible to calculate it without aggregating it (which would allow you to then build this part of the viz similar to your example above).

                               

                              Walt

                              • 12. Re: How to use an IF statement with aggregated data using the date as a condition
                                Filipe Hemsworth

                                Hi Walt,

                                 

                                The 'Organic Order Value' It's just a SUM() function using the code bellow:

                                 

                                IFNULL(SUM([Organic + Email Referrals].[Organic Refferal Value]),0)

                                +

                                SUM([Revenue])

                                 

                                My main data-source (Google Analytics) is JOINED on-to a simple Excel spreadsheet (Called [Organic + Email Referrals]) by Week number and Year:

                                 

                                The reason this field is aggregated whereas the other one is not, is because unfortunately our 'Organic Order Value' for our website is not being tracked correctly, meaning the value given by Google Analytics is not the full value. To get around this I have to do some manual work to calculate the remaining value, and add it on the the value given by Google Analytics.

                                 

                                I've included a few lines of the table I made that is being joined on to, where the additional revenue is being stored.

                                   

                                WeekYearReferral DateCategoryValue
                                Week 23201630/05/2016Email Order Value£906.45
                                Week 23201630/05/2016Email Orders24
                                Week 23201630/05/2016Organic Order Value£451.83
                                Week 23201630/05/2016Organic Orders13
                                Week 24201606/06/2016Email Order Value£832.89
                                Week 24201606/06/2016Email Orders21
                                Week 24201606/06/2016Organic Order Value£962.89
                                Week 24201606/06/2016Organic Orders27

                                 

                                I have then created a calculated field for each 'Category', to seperate out the values to use in future calculations. Here is the one I used to seperate out the additional Organic Order Value:

                                 

                                Organic Referral Value (additional revenue to add):

                                IF [Category] = "Organic Order Value" then [Value] ELSE NULL END

                                 

                                This is the value which is used in my originally aggregated field t hat i'm having issues with.....

                                 

                                Sorry this ended up being so long winded, but i thought it would be best to explain it in full.

                                 

                                Many thanks for your help.

                                • 13. Re: How to use an IF statement with aggregated data using the date as a condition
                                  Walt Reed

                                  Hey Felipe,

                                  Not being able to work with your data, I used the Sample Superstore dataset. First, I created a date parameter:

                                  Next, I created an aggregation (similar to your Organic Referral Value described above):

                                  Then, I created the three calculated fields you reference: this week, last week, this week last year:

                                  I then created a "% Difference" field to calculate the % change between this week and this week last year:

                                  Lastly, after adding the % Difference to the view, I changed the table calculation:

                                  Final output (you would need to filter out the other segments):

                                  I hope that this solution mirrors your situation--I tried my best to base it on the fields you described above. Let me know what you think.

                                   

                                  Walt

                                  • 14. Re: How to use an IF statement with aggregated data using the date as a condition
                                    Filipe Hemsworth

                                    Walt,

                                     

                                    I appreciate your help, however unfortunately you missed the step where the original aggregation took place, whereby I was bringing in the additional revenue from my joined spreadsheet.

                                     

                                    The calculation you used  'IF Segment = 'Consumer' THEN [Sales] END' is what i'm using just to select the additional revenue to be added on to my original Google Analytics value. Im then adding this value on to the main bulk of my sales coming from GA.

                                     

                                    I think i'm over-complicating this, and will probably just choose a more simple type of comparison to show how the value has changed over time for now. I think I could probably get your original method to work once I can figure out how the ATTR() is affecting the DATETRUNC('week',[Date]). As this seems to work, but something weird is happening where it will only display the results if I have a date dimension in the columns.

                                     

                                    IF ATTR(DATETRUNC('week',[Date])) = DATETRUNC('week',[Date Parameter]) THEN

                                    [Organic Order Value] END'

                                     

                                    Thanks again, i'll let you know if I come up with a solution!

                                    1 2 Previous Next