topic Re: Calculate YTD up to specific Date in QlikView

Calculate YTD up to specific Date

khaled — Fri, 15 Nov 2024 23:27:39 GMT

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

Re: Calculate YTD up to specific Date

Vegar — Sun, 13 Dec 2020 18:14:35 GMT

Try to make these adjustments to your expression:

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

Re: Calculate YTD up to specific Date

Anil_Babu_Samineni — Sun, 13 Dec 2020 20:03:17 GMT

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)

Re: Calculate YTD up to specific Date

khaled — Mon, 14 Dec 2020 22:21:52 GMT

Hello Vegar,

Thanks but the formula is not working

Re: Calculate YTD up to specific Date

khaled — Mon, 14 Dec 2020 22:23:36 GMT

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

Re: Calculate YTD up to specific Date

Kushal_Chawda — Mon, 14 Dec 2020 23:23:11 GMT

@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)

Re: Calculate YTD up to specific Date

khaled — Tue, 15 Dec 2020 07:38:55 GMT

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?

Re: Calculate YTD up to specific Date

Vegar — Tue, 15 Dec 2020 07:58:26 GMT

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 ...

Re: Calculate YTD up to specific Date

khaled — Wed, 16 Dec 2020 19:00:17 GMT

Hello Vegar,

I followed your step now Trx Date looks like 44143.593981481

I tried the formula again but it shows zero

Re: Calculate YTD up to specific Date

Vegar — Wed, 16 Dec 2020 19:52:49 GMT

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'))

Re: Calculate YTD up to specific Date

Anil_Babu_Samineni — Thu, 17 Dec 2020 07:02:23 GMT

Kindly share sample data set to handle this.

Re: Calculate YTD up to specific Date

khaled — Thu, 17 Dec 2020 08:37:54 GMT

Hello Anil,

Do you want me to share excel file data ?

or the qvd itself

Re: Calculate YTD up to specific Date

khaled — Thu, 17 Dec 2020 19:25:04 GMT

Hi Anil,

Kindly Check the Sample Data

Re: Calculate YTD up to specific Date

lfholland — Thu, 17 Dec 2020 20:25:53 GMT

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:
Load
min(TransactionDate) as minDate,
max(TransactionDate) as maxDate
Resident Link;

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)