Changing data format according to the data used

Question & Answer
, ,
1

Hello,

I have a parameter that controls the x axis of one visual. The parameter allow the decision between
day
month
week
quarter
and change the day according to that.
however the format in the x axis it is alwasy the same.

I would like that when it is day I have YYYY-MM-DD

when it is week it is WK
Month it shows the nameof the month
and when it is quarter it shows the quarte like Q1 Q2 etc…

this is the calculated field that updates the axis…

ifelse(

${seasonality}=“Day”,{received date DD},

${seasonality}=“Week”,{received date WK},

${seasonality}=“Month”,{received date MM},

${seasonality}=“Quarter”,{received date Q},NULL)

any suggestion?

thank you

2

Hi @remba87

something similar you can find in here

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

But you could also use 4 different visuals with the defined granularities and show/hide based on your parameter.

BR

1 Like
3

Hi @ErikG,

thank you!

my visual right now does excactly like the one you have shared. but the format of x axis does not change according to the Period,

If I select week, it would be cool to see the number of the week not the date…so yes, I think a solution is to use multiple visual and hide/show them according to the rule…I thought it could have been possible to set the format in the ifelse code for each different time period :slight_smile:

4

For Week, Month and Quarter you could use:
ifelse(
${seasonality} = ‘Week’, dateDiff(truncDate(“WK”, truncDate(‘YYYY’, {Order Date})), {Order Date}, ‘WK’)+1,
${seasonality} = ‘Quarter’, dateDiff(truncDate(“Q”, truncDate(‘YYYY’, {Order Date})), {Order Date}, ‘Q’)+1,NULL)
Like here explained: Week Numbers (1-53) - Learning Center / Articles - Amazon QuickSight Community
I am not yet sure how to combine the day part in it but I try to look into it

5

Hi @remba87
what about the different visuals workaround?
BR

6

Hi @remba87
did you found a solution?
BR

7

@remba87

I believe we have two potential solutions at hand.

  • use multiple visuals for each date level and show/hide. This way we plot with date column. (as suggested by @ErikG )
  • use one visual but for the axis transform date to string, as described by @robert.eibers

Hope one of these two worked out!

RL

8

Hello, this is a smart solution, the problem is that in the filter I can not add the day format because then it will create an error since some of the field will be number and some of the fitlers will be dates.

But yes if I do not need the day, this would be a good solution

9

Yes., I think the best solution is to do the Hide/show

thank you