Announcements
MAINTENANCE ALERT: Dec. 12th starting 9 AM CET. Community will be read-only. GET DETAILS
cancel
Showing results for
Did you mean:
Contributor

## Calculate YTD up to specific Date

Hello All,

I have a requirement to calculate the sum of sales from the start of the current year up to a selected date. let's say Today's date

which is 13-Dec-2020

so data will be from 01-Jan-2020 to 13-Dec-2020

Plus to calculate the same from the previous year up the same peer date

from 01-Jan-2019 to 13-Dec-2019

I use the below but it doesn't work at all

sum({<[Trx Date]=,Year=,Month=,Quarter=, [Trx Date]={">=\$(=YearStart(Max([Trx Date])))<=\$(=Today())"}>}Amount)

how to do this. please

Labels (1)
• ### ytd

13 Replies

Kindly share sample data set to handle this.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Contributor
Author

Hello Anil,

Do you want me to share excel file data ?

or the qvd itself

Contributor
Author

Hi Anil,

Kindly Check the Sample Data

Creator

In our load statement for our calendar table we create the following flag:

//Our "Link" table contains the transactions dates from our fact table.

Temp:
min(TransactionDate) as minDate,
max(TransactionDate) as maxDate

Let varMinDate = Num(Peek('minDate', 0, 'Temp'));

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

DROP Table Temp;

//Then in our Master Table script, we have the following statement

if(year(TempDate)<Year(addyears(Today(),-1)),1,(inyeartodate(makedate(year(today()), month(TempDate), day(TempDate)), today(), 0) * -1)) as CurPrevYTD,

We then use the flag in a set analysis formula like such:

sum({<Year={\$(=Max(Year)-1)}, CurPrevYTD={1}>} Sales)

Community Browser