One date widget, two date filters, plus an offset

5
(7)

It’s a bit surreal, this is the 200th blog I am publishing – what a wave of learnings it’s been (get it?). I was looking for a universal yet simple use case to cover for this 200th blog. A few times over the past week I’ve seen the question posted “how do I have one date widget, use the result for one measure but also subtract a year and apply that to another measure?”. Simple enough, I’ve done it before. Well, I also got challenged to simplify my solution, and oh boy that took me down the rabbit hole of bindings (aka interactions) and data serialization that opened up a can of worms of new data serializations I now need to try out. So while this might be the 200th blog, there is more “stuff” to cover on this great data and analytics platform. With that said, let’s just have a look at this use case.

The basics

Before we get to the bindings, let’s just make sure we are on the same page. On my dashboard, I have a date widget powered by the Close Date field from the opportunity dataset from the Sales Analytics templated app. So if you want to do this in your Trailhead org you can.

Note: A date widget can have many different properties around the date options a user has to select from. Believe it or not, this heavily influences the binding we will need to create later. Therefore, if you only allow absolute date ranges or relative, it may possibly mean that your binding and ultimately your approach can be different from what I am going to cover. I was trying to cover all my “unknowns” and that’s why I ended up with the solution in this blog.

For simplicity’s sake, the next widget will be a table and the query is a compare table that has two measures; amount filtered by this year and amount filtered by last year.

The idea with the compare table is that the current year amount will be controlled by the date widget’s instant selection, while the previous year will take the same selection and subtract a year.

Creating the query I start from a compare table and apply my filters – that’s always easier than going directly to writing SAQL – and apply the current year filter as normal. Now for my previous year filter, I just create a placeholder using amount instead of the Close Date. See the image below for an illustration of what I mean. I also grouped by Industry, but this step isn’t really relevant for the use case.

I’ll explain more in detail why I’m not using the date filter for the previous year as we progress with the use case, but for now, let’s move on with the query.

Next, I’ve switched my compare table to SAQL and I will now replace the reference to the amount field in my previous year filter with the epoch seconds version of the Close Date field. In the process, I renamed the two columns to make it easier for the user to read than ‘A’ and ‘B’.

Note: If you don’t know the API name you can always find it in the fields panel from the SAQL view.

q = load "opportunity";
--filter with result from date widget
q_A = filter q by date('CloseDate_Year', 'CloseDate_Month', 'CloseDate_Day') in ["current year".."current year"];
--filter with date offset
q_B = filter q by 'CloseDate_sec_epoch' >= 680 && 'CloseDate_sec_epoch' <= 8577295;
result = group q_A by 'Account.Industry' full, q_B by 'Account.Industry';
--Modified column names
result = foreach result generate coalesce(q_A.'Account.Industry', q_B.'Account.Industry') as 'Account.Industry', sum(q_A.'Amount') as 'Date Widget', sum(q_B.'Amount') as 'Minus 1 Year';
result = order result by ('Account.Industry' asc);
result = limit result 2000;

Calculate date offset

Before we get to the binding magic, we need a hidden step that does the calculation. I say hidden because the idea is that you do not add this query to a widget. I say that, but of course, for this example, I’m going to show it anyway so you can see what is actually happening behind the scene.

I’m sure you right now are thinking “Rikke why?”. I promise I’ll dedicate a section at the bottom for those that want to understand the reason for this technique, but to avoid confusion for those that just want a working solution I’m not going to overcomplicate this right now.

Using the same opportunity dataset I’m creating a new query and switching directly to SAQL. Here I am adding the aggregated measures min(CloseDate_sec_epoch) and max(CloseDate_sec_epoch). The idea here is that the date widget using regular faceting will filter the desired date range, which is then reflected by the min and max epoch seconds of Close Date.

As we want to use this query for the previous year, we need to calculate and add the date offset, which I have done in the query shown below.

q = load "opportunity";
q = group q by all;
--Calculate the date offset
q = foreach q generate min(CloseDate_sec_epoch) - (case 
        when toString(toDate(min(CloseDate_sec_epoch) - 365*24*3600), "dd")  == toString(toDate(min(CloseDate_sec_epoch)), "dd") then 365 
        else 366 
        end) 
    *24*3600 as 'min', max(CloseDate_sec_epoch) - (case 
        when toString(toDate(max(CloseDate_sec_epoch) - 365*24*3600), "dd")  == toString(toDate(max(CloseDate_sec_epoch)), "dd") then 365 
        else 366 
        end) 
    *24*3600 as 'max';
q = limit q 2000;

Let’s talk about line 4-12 where we are calculating the offset. We are calculating an offset for both the min and the max epoch seconds value. Let’s just look at the min value and break it down. In the query below, I have added comments throughout to explain what the query is doing.

--take the min Close Date and subtract the value from the case statement
min(CloseDate_sec_epoch) - 
   (case 
--Check for leap year by subtracting 365 days from the Close Date then comparing the day with current year. If they return the same value it wasn't a leap year and return 365
      when toString(toDate(min(CloseDate_sec_epoch) - 365*24*3600), "dd")  == toString(toDate(min(CloseDate_sec_epoch)), "dd") then 365 
--If it was a leap year return 366
      else 366 
      end) 
--Calculate how long the year is in seconds
    *24*3600 
as 'min',

If you don’t trust me try the query below – there I’ve converted the actual min and max plus the calculated min and max to an actual date field. This should make it easier for you to read than looking at epoch seconds.

q = load "opportunity";
q = group q by all;
q = foreach q generate min(CloseDate_sec_epoch) - (case when toString(toDate(min(CloseDate_sec_epoch) - 365*24*3600), "dd")  == toString(toDate(min(CloseDate_sec_epoch)), "dd") then 365 else 366 end) *24*3600 as 'min', max(CloseDate_sec_epoch) - (case when toString(toDate(max(CloseDate_sec_epoch) - 365*24*3600), "dd")  == toString(toDate(max(CloseDate_sec_epoch)), "dd") then 365 else 366 end) *24*3600 as 'max', min(CloseDate_sec_epoch) as 'minForget', max(CloseDate_sec_epoch) as 'maxForget';
--Below statement converts epoch seconds into date fields so you can compare
q = foreach q generate toString(toDate(q.'min'), "yyyy-MM-dd") as 'minPrevYear', toString(toDate(q.'max'), "yyyy-MM-dd") as 'maxPrevYear', toString(toDate(q.'minForget'), "yyyy-MM-dd") as 'minActual', toString(toDate(q.'maxForget'), "yyyy-MM-dd") as 'maxActual', 'min', 'max';
q = limit q 2000;

Before you save and add the query (if you want to do that) make sure you are using the first query that returns just the min and max date offset values.

Bind it!

Alright, we are almost at the magic part. But before we add the binding make sure you exclude the query from faceting that powers the date widget from the compare table we created with the two dates. Why? Well, the selection from the date widget will be applied as a pre-projection filter, thus filtering both streams, which means we will never get a result in the ‘Minus 1 year’ column.

Now let’s open the Advanced Editor. You can use the editor to select the data selection and serialization to fit the below bindings – or you can copy-paste given you have named your queries the same as me and stuck to the same aliases (API names).

Note: If you are new to bindings, please read through my binding blog series. As a minimum, you need to understand part 1 and part 2, where the examples of the syntax we will be using are explained.

Let’s look at the first filter where we look at the current year or what is supposed to be controlled by the result of the date widget.

q_A = filter q by date('CloseDate_Year', 'CloseDate_Month', 'CloseDate_Day') in [\"current year\"..\"current year\"];

With the binding, the filter will look like the below.

q_A = filter q by {{row(CloseDate_1.selection, [0], [\"min\", \"max\"]).asDateRange(\"date('CloseDate_Year', 'CloseDate_Month', 'CloseDate_Day')\")}};

The second filter that will be showing the selected date range minus one year will be powered by the hidden query or step.

q_B = filter q by 'CloseDate_sec_epoch' >= 680 && 'CloseDate_sec_epoch' <= 8577295;

With the binding, the filter will look like the below.

q_B = filter q by {{row(dateOffset_1.result, [0], [\"min\", \"max\"]).asRange(\"CloseDate_sec_epoch\")}};

In the end, your query will look something like the below example. I know, it can be a little hard to read in the advanced editor.

"q = load \"opportunity\";\n--filter with result from date widget\nq_A = filter q by {{row(CloseDate_1.selection, [0], [\"min\", \"max\"]).asDateRange(\"date('CloseDate_Year', 'CloseDate_Month', 'CloseDate_Day')\")}};\n--filter with date offset\nq_B = filter q by {{row(dateOffset_1.result, [0], [\"min\", \"max\"]).asRange(\"CloseDate_sec_epoch\")}};\nresult = group q_A by 'Account.Industry' full, q_B by 'Account.Industry';\n--Modified column names\nresult = foreach result generate coalesce(q_A.'Account.Industry', q_B.'Account.Industry') as 'Account.Industry', sum(q_A.'Amount') as 'Date Widget', sum(q_B.'Amount') as 'Minus 1 Year';\nresult = order result by ('Account.Industry' asc);\nresult = limit result 2000;"

That’s it. You now have one date widget powering one query with the selected date and an offset.

Why the hidden step?

As you probably know there are different data serialization options, each powerful in its own way in controlling the output of the data selection. If we know the input and the desired output we can select the fitting data serialization, however the date widget together with the offset requirement is a challenge.

In a date widget where all options for date selections remain (pre-defined relative dates, relative dates, and absolute dates) the min and max format of the data differs. Relative dates are in an array such as [fiscal_year, 1], where absolute date ranges use epoch seconds. The data serializations .asDateRange() and doesn’t care and will convert it to fit the date filter syntax using either relative or absolute date range. This works great for the first filter. You can also use the .asRange() with the epoch seconds, as this also will look to always have a numeric output instead of the object/array.

However, when we need to calculate an offset we need to modify the output further, which we are doing in the hidden step. A personal preference when I work with dates is to use the epoch second version where possible. Thus I am just using the .asRange() as there in this case, really is no need to convert it back to a date.

That’s it

And that was blog 200. Thanks for reading this. And if this isn’t the first blog you have read then thanks for following along on the journey for the past 5 years.

How useful was this post?

Click on a star to rate useful the post is!

Written by


9 thoughts on “One date widget, two date filters, plus an offset”

  • 1
    kaush on March 4, 2022 Reply

    This looks familiar 🙂

  • 3
    Evan Emerson on March 12, 2022 Reply

    Congrats and thank you for 200 amazing blog posts!

    I would be so lost without you and your help.

    Sincerely and genuinely Thank You!

    your blogs have helped me, countless times saved me and are one of the two main reasons I have had any success at all within the Einstein Analytics world

    • 4
      Rikke on March 14, 2022 Reply

      Thanks for the kind words, Evan! Glad it’s been helpful for you.

  • 5
    John Lawrence Catan on March 12, 2022 Reply

    Thank you for this. I’ve had this requirement and didn’t know it could be done with a hidden step. This was very helpful especially with the explanation of the approach 🙂

  • 6
    Roshan Menon on July 12, 2022 Reply

    Thank you Rikke.I have learnt a lot from you.This is very helpful.I was trying to do relative filter in the dataflow [“4 quarters ago”..”previous quarter”]. This filter is not giving any result to me but when I am using fixed dates for the same time period then I am able to see the result.Actually any filter beyond “2 quarters ago” is not working in the dataflow. Please let me know if I am missing out on anything.

  • 7
    Francis Crump on January 9, 2023 Reply

    I’ve tried this in Timezone Enabled orgs and it errors. Can’t use toDate, any idea on solution for that?

  • 8
    Inho Kim on April 27, 2023 Reply

    Your solution for 1 year ago is really helpful thanks!
    [
    min(erp_sales_date__c_formula_sec_epoch)
    – (case
    when toString(toDate(min(erp_sales_date__c_formula_sec_epoch) – 365*24*3600), “dd”) == toString(toDate(min(erp_sales_date__c_formula_sec_epoch)), “dd”) then 365
    else 366
    end)
    *24*3600 as ‘min’
    ]

    but my client want

    1 week ,1 month , 1day ago also…
    but i wonder week / month /day might need
    case ~ end keyword to some exceptions .
    ( like you took cared about ‘leap year’ when processing 1 year ago )

    Would you give me references or blog about processing 1month/ 1week/ 1day ago ?

  • 9
    Nadia Barabash on January 29, 2024 Reply

    I love this solution! Thanks Rikke

Leave a Reply to kaush Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.