Calculate YTD up to specific Date

[khaled]

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

[Vegar]

Try to make these adjustments to your expression:

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

[Anil_Babu_Samineni]

Perhaps this  and make sure the date formats If you need to adjust.

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

[khaled]

Hello Vegar,

Thanks but the formula is not working

[khaled]

Hello Anil,

I need to calculate the amount as of Today's Date. the provided formula works as per the whole month.

for example 

if today is 10-Dec-2020 I need to get the data as of 10 not as of 31th 

Thanks

[Kushal_Chawda]

@khaled  make sure that your date field is in proper date format and not text. try to check max(Date) in text object, if it returns value then it is in proper format.

Try below if your Date field is properly formatted

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

[khaled]

Hello @Kushal_Chawda 

The Trx Date field is a date in the DB  of format DD-MON-RRRR HH24:MI:SS and I didn't format it, Should I?

[Vegar]

The string format is not that important when using >= and <, however it is important that the [Trx Date] intepreted as a numeric (timestamp or date) by QlikView.

Try loading it into the datamodel as follows. Assuming 'MON' is the same as 'MMM' in QlikView. 

LOAD 
timestamp#([Trx Date], 'DD-MMM-YYYY hh:mm:ss') as [Trx Date]
FROM ...

[khaled]

Hello Vegar,

I followed your step now Trx Date looks like 44143.593981481

I tried the formula again but it shows zero 

[Vegar]

That's the numeric representation of an timestamp. 

If you need to present it as a timestamp you can wrap timestamp () around it, if you only need the date part without the time of day then wrap dayname() around it like this timestamp (timestamp#([Trx Date], 'DD-MMM-YYYY hh:mm:ss'))  or dayname( timestamp#([Trx Date], 'DD-MMM-YYYY hh:mm:ss'))