PY YTD and PY MTD

Q&A
, ,
1

Hi everyone, I’m currently trying to calculate a PY YTD and PY MTD metric for a sum of XEVs variable. I’ve tried many different ways, either by creating a field for PY date (vehicle_retail_date_py), either by calculating it by hand, and the closest I managed to achieve, is the following code, but I stand on a specific and rather strange error:

“Expression addDateTime(-1, ‘YEAR’, now()) for function addDateTime has incorrect argument type addDateTime(Number, String, Date). Function syntax expects Numeric, String, Date.”

Could you explain to me the difference between a “Number” and a “Numeric” in Quicksight data types?

How could I fix this code?

ifelse(
    ${Period} = "YTD",
    sum(
        ifelse(
            vehicle_retail_date >= formatDate(
                concat(
                    formatDate(
                        extract('YYYY', addDateTime(-1, 'YEAR', now())),
                        '0000'
                    ),
                    '-',
                    formatDate(
                        extract('MM', addDateTime(-1, 'YEAR', now())),
                        '00'
                    ),
                    '-01'
                ),
                'yyyy-MM-dd'
            )
            AND vehicle_retail_date <= formatDate(now(), 'yyyy-MM-dd'),
            XEVs,
            NULL
        )
    ) ,
    ${Period} = "MTD",
    sum(
        ifelse(
            extract('DD', now()) - extract('DD', {vehicle_retail_date}) >= 0,
            XEVs,
            NULL
        )
    ),
    ${Period} = "ACT",
    sum(XEVs),
    0
)
2

Hi @GabrielLF ,

Welcome to the Quick Sight Community!!

Before I jump on to understand the formula, can you try using ‘YYYY’ instead or ‘YEAR’ in addDateTime function?

You can refer to the required format here.

Thanks,
Prantika

3

surely, @prantika_sinha
it gives me the following error:

Expression formatDate(
            concat(
                formatDate(
                    extract('YYYY', addDateTime(-1, 'YYYY', now())),
                    '0000'
                ),
                '-',
                formatDate(
                    extract('MM', addDateTime(-1, 'YYYY', now())),
                    '00'
                ),
                '-01'
            ),
            'yyyy-MM-dd'
        ) for function formatDate has incorrect argument type formatDate(Number, String). Function syntax expects  Date, String (Period).
4

Can you remove the formatDate before each extract and replace it with toString?
What I understand is you are trying to fetch Year, month and default it as first day of the month to create previous year month start date.
However for single digit month numbers, you will need to add a condition to pad 0 on the left.

But a quick check, are you trying to fetch start of the month last year based on current date ?
You can try:
truncDate(‘MM’, addDate(-1,‘YYYY’,now())) could be a quick resolution to this.

Thanks.

5

Is simpler than that. I Want to do a PY YTD. Basically, the YTD (all the days between 1st of January and now()), but for the previous year. I want to be able to create a double bar chart, with XEVs Sum on one side, and XEVs Sum PY on the other, but I have a parameter for MTD and YTD, and would like it to apply to my PY aswell.

For doing only the PY part, I used this and worked:

periodOverPeriodLastValue(sum(XEVs), {vehicle_retail_date}, YEAR, 1)

but this can’t be used inside the ifelse for the parameter, because it would contain a nested agreggation.

For the ‘toString’ solution, it gives the following error:

Function `toString` should have 1 argument(s) instead of 4 argument(s).

This is a working code, for the not-previous year version:

ifelse
(
${Period} = "YTD",
    sum(ifelse(
    (extract('YYYY', {vehicle_retail_date}) = extract('YYYY', now()) AND 
    dateDiff({vehicle_retail_date}, now(), 'DD') >= 0) OR 
    (extract('YYYY', {vehicle_retail_date}) = extract('YYYY', addDateTime(-1, 'YYYY', now())) AND 
    dateDiff({vehicle_retail_date}, addDateTime(-1, 'YYYY', now()), 'DD') >= 0),
    XEVs,
    NULL
))
,
${Period} = "MTD",
    sum(ifelse(extract('DD', now()) - extract('DD',{vehicle_retail_date}) >= 0, XEVs, NULL))
,
${Period} = "ACT",
sum(XEVs)
,0)

but as I mentioned, trying to use the same logic and use the PeriodOverPeriodLastValue inside the ifelse was impossible.

6

@GabrielLF, is this issue resolved? Do you still need help with PeriodOverPeriodLastValue. This post maybe of help:

7

Did this solution work for you? I am marking this reply as, “Solution,” but let us know if this is not resolved. Thanks for posting your questions on the Quick Sight Community!