Date Formatting

Q&A
, , , , ,
1

I am trying to get the the following date formats from my closing date field which is a standard YYYY-MM-DD field. I have a parameter that is able to toggle between different aggregations such as just viewing month, day, year, or quarter.

I am trying to format the date so it displays the following:

When clicking on Year its just the year
when clicking on Month it displays MM-YYYY
When clicking on Quarter it displays Q-YYYY
and when clicking on day it displays DD-YYYY

This is the calculation I am using so far:

ifelse(
${PeriodStarting}=‘Day’,toString(extract(‘DD’,{Closing Date})),
${PeriodStarting}=‘Month’,toString(extract(‘MM’,{Closing Date})),
${PeriodStarting}=‘Quarter’,toString(extract(‘Q’,{Closing Date})),
toString(extract(‘YYYY’,{Closing Date})))

When I do a concatenation of this toString(extract(‘DD’,{Closing Date})) and toString(extract(‘YYYY’,{Closing Date})) the day just stays the same but the year changes. I need both to change together.

1 Like
2

Hi @Entrepreneurldz
could you share a sample table viz with “closing date” and the two strings as single fields?
BR

1 Like
3

Hello, do you mean as the the concatenation? Not sure what you mean by two strings as single fields.

4

@ErikG concat(toString(extract(‘DD’,{Closing Date})),‘-’,toString(extract(‘YYYY’,{Closing Date}))),

Basically shows this 9-2014 9-2013 9-2012 9-2011

I need this:

9-2014 8-2014 7-2014 6-2014 etc.

5

If you create a simple table visual with 3 columns/fields (closing date, the “day string” and the “year string”)

1 Like
6

That works when I do it as separate fields but it is possible to have it just as one field as in DD-YYYY

7

I know, but I want so see if the day is matching the date. Because the formula looks right so far.

1 Like
8

Hi @Entrepreneurldz,

If I understand correctly, you’re final calculated field should be:

ifelse(
${PeriodStarting}='Day',concat(toString(extract('DD',{Closing Date})),'-',toString(extract('YYYY',{Closing Date}))),
${PeriodStarting}='Month',concat(toString(extract('MM',{Closing Date})),'-',toString(extract('YYYY',{Closing Date}))),
${PeriodStarting}='Quarter',concat(toString(extract('Q',{Closing Date})),'-',toString(extract('YYYY',{Closing Date}))),
toString(extract('YYYY',{Closing Date})))

As per @ErikG’s comment, the formula appears to be correct and produces results for me as shown here:

image
image773×531 35 KB

Does this help?

Many Thanks,
Andrew

1 Like
9

Hello @Entrepreneurldz !

Was @abacon 's comment above helpful and if so could you mark their post as a solution?

We have not heard from you in a few days but would still like to help find a solution. If we do not hear from you in 3 days this post will be archived.