Show Dynamic Columns Based on Today’s Date in Tableau CRM

I don’t know about you, but I feel like every request I get really pushes the envelope of what can be done inside of a dashboard. You give someone something and then they want something even cooler and they don’t want to have to click to get it. Such is the case for my latest request, where the user wanted to see different columns based on today’s date. Basically, we wanted this table to be granular enough for the time period they care about (the current period), with broader summaries for past periods. For example, if today is in Q1, then I might want to see a Q1 total, along with each month in the quarter, along with the half and the year. But if today was in Q2, I wouldn’t care about the months in Q1 anymore, I’d case about the months in Q2 and so Q1 would be collapsed and I’d show a Q1 total, Q2 total, each month in Q2, the half, and the year. I think you get the picture. Rather than having to select what to show when by using a static list selector, they wanted this to be dynamic based on today’s date. I’m sure there are a few different ways to accomplish this, but let me show you how I did it!

Date Columns

I chose to calculate my dates and the resulting foreach statements in a separate query instead of in the query that’s actually projecting my table. This doesn’t necessarily make it more efficient at run-time, but I thought it would make it easier to maintain and update later on (for both queries). Then, I’ll apply a binding in the final foreach statement of the details table so that I only project the columns I want to see, even though I’ve calculated every time period. I went back and forth on this bit, but decided to just go ahead and calculate all periods because it makes the final foreach statement simpler. You could use commenting or similar bindings to only run the queries that you need to calculate for the given time period too. So here is my actual query, and I’ll add comments in line for context:

-- this is actually a csv dataset with just like 50 years of dates in it that I like to use for stuff like this. You could do this query with any dataset though because I'm not actually using any rows from the dataset.

q = load "masterDates";
q = group q by all;


-- now we're going to use a case statement that is basically just saying, if now() is in a particular month, then project what I want to see for that month. And I do that 12 times, one for each month.

q = foreach q generate case 

    when substr(toString(now(), "yyyy-MM-dd"),6,2) in ["07"] then "coalesce(sum('today'),0) as 'today', coalesce(sum('q1'),0)+coalesce(sum('today'),0) as 'q1', coalesce(sum('q1m1'),0)+coalesce(sum('today'),0) as 'q1m1', coalesce(sum('q1m2'),0) as 'q1m2', coalesce(sum('q1m3'),0) as 'q1m3', coalesce(sum('h1'),0)+coalesce(sum('today') as 'h1', coalesce(sum('y'),0)+coalesce(sum('today') as 'y'" 
    when substr(toString(now(), "yyyy-MM-dd"),6,2) in ["08"] then "coalesce(sum('today'),0) as 'today', coalesce(sum('q1'),0)+coalesce(sum('today'),0) as 'q1', coalesce(sum('q1m1'),0) as 'q1m1', coalesce(sum('q1m2'),0)+coalesce(sum('today'),0) as 'q1m2', coalesce(sum('q1m3'),0) as 'q1m3', coalesce(sum('h1'),0)+coalesce(sum('today'),0) as 'h1', coalesce(sum('y'),0)+coalesce(sum('today'),0) as 'y'" 
    when substr(toString(now(), "yyyy-MM-dd"),6,2) in ["09"] then "coalesce(sum('today'),0) as 'today', coalesce(sum('q1'),0)+coalesce(sum('today'),0) as 'q1', coalesce(sum('q1m1'),0) as 'q1m1', coalesce(sum('q1m2'),0) as 'q1m2', coalesce(sum('q1m3'),0)+coalesce(sum('today'),0) as 'q1m3', coalesce(sum('h1'),0)+coalesce(sum('today'),0) as 'h1', coalesce(sum('y'),0)+coalesce(sum('today'),0) as 'y'" 


    when substr(toString(now(), "yyyy-MM-dd"),6,2) in ["10"] then "coalesce(sum('today'),0) as 'today', coalesce(sum('q1'),0) as 'q1', coalesce(sum('q2'),0)+coalesce(sum('today'),0) as 'q2', coalesce(sum('q2m1'),0)+coalesce(sum('today'),0) as 'q2m1', coalesce(sum('q2m2'),0) as 'q2m2', coalesce(sum('q2m3'),0) as 'q2m3', coalesce(sum('h1'),0)+coalesce(sum('today'),0) as 'h1', coalesce(sum('y'),0)+coalesce(sum('today'),0) as 'y'"
    when substr(toString(now(), "yyyy-MM-dd"),6,2) in ["11"] then "coalesce(sum('today'),0) as 'today', coalesce(sum('q1'),0) as 'q1', coalesce(sum('q2'),0)+coalesce(sum('today'),0) as 'q2', coalesce(sum('q2m1'),0) as 'q2m1', coalesce(sum('q2m2'),0)+coalesce(sum('today'),0) as 'q2m2', coalesce(sum('q2m3'),0) as 'q2m3', coalesce(sum('h1'),0)+coalesce(sum('today'),0) as 'h1', coalesce(sum('y'),0)+coalesce(sum('today'),0) as 'y'"
    when substr(toString(now(), "yyyy-MM-dd"),6,2) in ["12"] then "coalesce(sum('today'),0) as 'today', coalesce(sum('q1'),0) as 'q1', coalesce(sum('q2'),0)+coalesce(sum('today'),0) as 'q2', coalesce(sum('q2m1'),0) as 'q2m1', coalesce(sum('q2m2'),0) as 'q2m2', coalesce(sum('q2m3'),0)+coalesce(sum('today'),0) as 'q2m3', coalesce(sum('h1'),0)+coalesce(sum('today'),0) as 'h1', coalesce(sum('y'),0)+coalesce(sum('today'),0) as 'y'"
    
    
    when substr(toString(now(), "yyyy-MM-dd"),6,2) in ["01"] then "coalesce(sum('today'),0) as 'today', coalesce(sum('q1'),0) as 'q1', coalesce(sum('q2'),0) as 'q2', coalesce(sum('h1'),0) as 'h1', coalesce(sum('q3'),0)+coalesce(sum('today'),0) as 'q3', coalesce(sum('q3m1'),0)+coalesce(sum('today'),0) as 'q3m1', coalesce(sum('q3m2'),0) as 'q3m2', coalesce(sum('q3m3'),0) as 'q3m3', coalesce(sum('h2'),0)+coalesce(sum('today'),0) as 'h2', coalesce(sum('y'),0)+coalesce(sum('today'),0) as 'y'"
    when substr(toString(now(), "yyyy-MM-dd"),6,2) in ["02"] then "coalesce(sum('today'),0) as 'today', coalesce(sum('q1'),0) as 'q1', coalesce(sum('q2'),0) as 'q2', coalesce(sum('h1'),0) as 'h1', coalesce(sum('q3'),0)+coalesce(sum('today'),0) as 'q3', coalesce(sum('q3m1'),0) as 'q3m1', coalesce(sum('q3m2'),0)+coalesce(sum('today'),0) as 'q3m2', coalesce(sum('q3m3'),0) as 'q3m3', coalesce(sum('h2'),0)+coalesce(sum('today'),0) as 'h2', coalesce(sum('y'),0)+coalesce(sum('today'),0) as 'y'"
    when substr(toString(now(), "yyyy-MM-dd"),6,2) in ["03"] then "coalesce(sum('today'),0) as 'today', coalesce(sum('q1'),0) as 'q1', coalesce(sum('q2'),0) as 'q2', coalesce(sum('h1'),0) as 'h1', coalesce(sum('q3'),0)+coalesce(sum('today'),0) as 'q3', coalesce(sum('q3m1'),0) as 'q3m1', coalesce(sum('q3m2'),0) as 'q3m2', coalesce(sum('q3m3'),0)+coalesce(sum('today'),0) as 'q3m3', coalesce(sum('h2'),0)+coalesce(sum('today'),0) as 'h2', coalesce(sum('y'),0)+coalesce(sum('today'),0) as 'y'"
    
    
    when substr(toString(now(), "yyyy-MM-dd"),6,2) in ["04"] then "coalesce(sum('today'),0) as 'today', coalesce(sum('q1'),0) as 'q1', coalesce(sum('q2'),0) as 'q2', coalesce(sum('h1'),0) as 'h1', coalesce(sum('q3'),0) as 'q3', coalesce(sum('q4'),0)+coalesce(sum('today'),0) as 'q4', coalesce(sum('q4m1'),0)+coalesce(sum('today'),0) as 'q4m1', coalesce(sum('q4m2'),0) as 'q4m2', coalesce(sum('q4m3'),0) as 'q4m3', coalesce(sum('h2'),0)+coalesce(sum('today'),0) as 'h2', coalesce(sum('y'),0)+coalesce(sum('today'),0) as 'y'" 
    when substr(toString(now(), "yyyy-MM-dd"),6,2) in ["05"] then "coalesce(sum('today'),0) as 'today', coalesce(sum('q1'),0) as 'q1', coalesce(sum('q2'),0) as 'q2', coalesce(sum('h1'),0) as 'h1', coalesce(sum('q3'),0) as 'q3', coalesce(sum('q4'),0)+coalesce(sum('today'),0) as 'q4', coalesce(sum('q4m1'),0) as 'q4m1', coalesce(sum('q4m2'),0)+coalesce(sum('today'),0) as 'q4m2', coalesce(sum('q4m3'),0) as 'q4m3', coalesce(sum('h2'),0)+coalesce(sum('today'),0) as 'h2', coalesce(sum('y'),0)+coalesce(sum('today'),0) as 'y'" 
    when substr(toString(now(), "yyyy-MM-dd"),6,2) in ["06"] then "coalesce(sum('today'),0) as 'today', coalesce(sum('q1'),0) as 'q1', coalesce(sum('q2'),0) as 'q2', coalesce(sum('h1'),0) as 'h1', coalesce(sum('q3'),0) as 'q3', coalesce(sum('q4'),0)+coalesce(sum('today'),0) as 'q4', coalesce(sum('q4m1'),0) as 'q4m1', coalesce(sum('q4m2'),0) as 'q4m2', coalesce(sum('q4m3'),0)+coalesce(sum('today'),0) as 'q4m3', coalesce(sum('h2'),0)+coalesce(sum('today'),0) as 'h2', coalesce(sum('y'),0)+coalesce(sum('today'),0) as 'y'" 
    
end as 'datecolumns';

Apply Your Binding

Once we have that query written, we can use it to project the correct month’s columns by applying a binding in the foreach statement of our table query.

{{column(date_columns_1.result, ["datecolumns"]).asObject()}}

Here is an example of my final foreach statement:

final = foreach final generate case when grouping('Opportunity.Sales_Team__c') == 1 then "Total" else 'Opportunity.Sales_Team__c' end as 'Sales Team', {{column(date_columns_1.result, ["datecolumns"]).asObject()}};

And since today’s date is in our Q1, these are the columns that get displayed:

On October 1, these columns will change following this logic:

If TODAY is in Q1, show me: Today Q1 Q1M1 Q1M2 Q1M3 H1 Y

If TODAY is in Q2, show me: Today Q1 Q2 Q2M1 Q2M2 Q2M3 H1 Y

If TODAY is in Q3, show me: Today Q1 Q2 H1 Q3 Q3M1 Q3M2 Q3M3 H2 Y

If TODAY is in Q4, show me: Today Q1 Q2 H1 Q3 Q4 Q4M1 Q4M2 Q4M3 H2 Y

How cool is that?!