Changing Date Aggregation with Parameter

Question & Answer
, ,
1

I’m trying to follow this example in the demo site:

https://democentral.learnquicksight.online/#Dashboard-TipsAndTricks-Calculation-Switch-Date-Aggregation

I have a parameter dropdown control with a list of values and I’d like to control the date grouping in my chart based on the selection. My calculated field looks like this:

ifelse(
${DateRangeParm}=‘Last Month’,truncDate(“MM”,Date),
${DateRangeParm}=‘Last 3 Months’,truncDate(“MM”,Date),
${DateRangeParm}=‘Last 6 Months’,truncDate(“MM”,Date),
${DateRangeParm}=‘Last Year’,truncDate(“MM”,Date),
${DateRangeParm}=‘Last 4 Quarters’,truncDate(“Q”,Date),
${DateRangeParm}=‘Year’,truncDate(“YYYY”,Date),
truncDate(“MM”,Date)
)

While this does work, I can’t figure out how to format the data on my x-axis based on the DateRangeParm. If it’s one of the monthly formats, I want it formatted “Jan 21”. If it’s quarterly, I would like “Q1 21”, and yearly should be “2021”.

This is what it looks like when I select “Last 6 Months”
image

Here is “Last 4 Quarters”
image

I tried doing a dateFormat around the truncDate functions but that gives me an error.

2

Hi Corey, to display the month you can use a calculation like:
concat(left(formatDate(Date,‘MMM/dd/yyyy’),3)," ",right(formatDate(Date,‘MMM/dd/yyyy’),2))

and for the Quarter you can use:
concat(“Q”, toString(extract(“Q”,Date))," ",right(formatDate(Date,‘MMM/dd/yyyy’),2)).

3

Thanks for your help, but I’m still getting some sort of error. Here is what I have in my calculated field. I changed just one of my options, “Last Month”, as a test.

ifelse(
${DateRangeParm}=‘Last Month’,concat(left(formatDate(truncDate(“MM”,Date),‘MMM/dd/yyyy’),3)," ",right(formatDate(truncDate(“MM”,Date),‘MMM/dd/yyyy’),2)),
${DateRangeParm}=‘Last 3 Months’,truncDate(“MM”,Date),
${DateRangeParm}=‘Last 6 Months’,truncDate(“MM”,Date),
${DateRangeParm}=‘Last Year’,truncDate(“MM”,Date),
${DateRangeParm}=‘Last 4 Quarters’,truncDate(“Q”,Date),
${DateRangeParm}=‘Year’,truncDate(“YYYY”,Date),
truncDate(“MM”,Date)
)

image
image930×337 24.3 KB

4

Hi Corey, the calculation that I provided returns a string like ‘Jan 21’ or ‘Q1 21’ like you were trying to achieve. truncDate returns a date based on the period(documentation). The return values of all conditions should be of the same datatype. Also make sure that that the single quotes or double quotes are proper quotes, sometimes copy paste changes the single quotes like the one in MMM/dd/yyyy.

1 Like
5

Salim,

Thanks for your help but I still can’t get it to work. The problem may be more in the context of what I’m trying to do overall.

In the below screenshot, I have my Date Range parameter control and a calculated field called DateLevelCalc. This calc field is used on my x-axis.

I want multiple things to happen when the user picks a choice from the Date Range dropdown:

  1. Change the x-axis to the appropriate date aggregation
  2. Filter the data according to the date aggregation choice
  3. Format the dates on the x-axis according to the date aggregation choice

#3 is the one I can’t seem to get working with your suggestion. I was able to get the calc field to save, but I get a contextual invalid argument type error in the chart.

Is what I’m trying to accomplish possible?

image
image880×829 67.5 KB

6

I am still waiting on either a solution for this problem or if it is not supported I would like to have it entered as a feature request.

7

I think I got it. Let me know if it works.

You’ll need to sub the param and the date.

ifelse(${Client}=‘Last Month’ OR ${Client}=‘Last 3 Months’ OR ${Client}=‘Last 6 Months’ OR ${Client}=‘Last Year’,

concat(

left(

toString(

formatDate(

truncDate(‘MM’,{arrival_timestamp})

,‘MMM/dd/yyyy’)

)

,3)

," ",

right(

toString(

formatDate(

truncDate(‘MM’,{arrival_timestamp})

,‘MMM/dd/yyyy’)

)

,2)

),${Client}=‘Last 4 Quaters’,

concat(

‘Q’,toString(extract(‘Q’,{arrival_timestamp}))," ",toString(extract(‘YYYY’,{arrival_timestamp}))

),

${Client}=‘Year’,

toString(extract(‘YYYY’,{arrival_timestamp})),null

)

This will return a string. So you’ll need to find a way to sort on in. I would recommend making a separate calculated field that takes the difference from now until the date as a number and sort on that average.

datDiff({date},now(),‘DD’)

1 Like