Possible Date Format Issue in YTD

[FlAsh92]

Hey guys, I'm new to Qlik Sense and currently trying to generate a YTD table.

Used guidance from the post below:

https://community.qlik.com/t5/QlikView-Documents/YTQ-QTD-MTD-and-WTD/ta-p/1489245

But the values that populate in the column do not match my data.

Just to give a brief, I inward the Oracle data through a SQL connector. The format of my date field is 'yyyy-mm-dd hh:mm:ss.ssss' and I've labelled it as T_Date. I also get the Year, Month and Day in separate columns which can be used in case the above date format is the issue.

In the Data Load Editor I used the script:

Floor(DateField) AS DateNum

I figured this code separates out the Date part only.

The I moved on to the YTD expression:

Sum({<[T_Date.autoCalendar.Year]=, [T_Date.autoCalendar.Month]=, [T_Date.autoCalendar.Quarter]=, [T_Date.autoCalendar.Week]=, [T_Date.autoCalendar.Date]=, DateNum={">=$(=Num(YearStart(Max(DateNum))))<=$(=Max(DateNum))"}>} T_Value)

I'm not sure if the above expression is right, because it displays the wrong summation value.

Also, I've not defined any specific variables for this.

Please help me and guide me on the issue.

[isorinrusu]

Hi,

The best way to check why that's not working is by separating the elements.

You should create a variable called vYearStartNum (for example) containing the calculation 

=Num(YearStart(Max(DateNum)))

And one for the max date (although maybe you don't need it if you're data is from the fact table, not containing future dates): vMaxDateNum 

=Max(DateNum)

So your expression would be:

Sum({<[T_Date.autoCalendar.Year]=, [T_Date.autoCalendar.Month]=, [T_Date.autoCalendar.Quarter]=, [T_Date.autoCalendar.Week]=, [T_Date.autoCalendar.Date]=, DateNum={">=$(vYearStartNum)<=$(vMaxDateNum)"}>} T_Value)

 If it doesn't work, try putting the variables in two separate KPIs (or table) to see the result, to see if the dates are calculated correctly.

Hope that helps.

Regards,

Sorin.

[FlAsh92]

Hey @isorinrusu ,

I tried you solution, added the variables and made changes to the expression.

But it still doesn't seem to work. Hence i split the expression like you suggested, I'm not sure if my expression is right, so forgive me if I'm wrong. I'm attaching all the expressions along with a screenshot.

YTD New:

Sum({<[T_Date.autoCalendar.Year]=, [T_Date.autoCalendar.Month]=, [T_Date.autoCalendar.Quarter]=, [T_Date.autoCalendar.Week]=, [T_Date.autoCalendar.Date]=, DateNum={"=$(vYearStartNum)<=$(vMaxDateNum)"}>} T_Value)

YTD New Part -1 (Split Expression):

Sum({<[T_Date.autoCalendar.Year]=, [T_Date.autoCalendar.Month]=, [T_Date.autoCalendar.Quarter]=, [T_Date.autoCalendar.Week]=, [T_Date.autoCalendar.Date]=, DateNum={"$(vYearStartNum)"}>} T_Value)

YTD New Part -2 (Split Expression):

Sum({<[T_Date.autoCalendar.Year]=, [T_Date.autoCalendar.Month]=, [T_Date.autoCalendar.Quarter]=, [T_Date.autoCalendar.Week]=, [T_Date.autoCalendar.Date]=, DateNum={"$(vMaxDateNum)"}>} T_Value)

Sum Value:

Sum([T_Value])

In the screen shots attached, I've selected the same date(1st, January 2019), but tried 2 types of date selection. In 'YTD Error', I've selected the Year as '2019', then the Month as 'Jan' from the autoCalendar and the day from my Fact Table column. In 'YTD Error-1', I've selected '01/01/2019' from the autoCalendar. I selected this particular date because, the Sum Value and YTD New should have the same value on the first day of the year and thereby the comparison is easier (NOTE: The fact table contains values from 1st Jan, 2018). The KPIs are showing the Sales figures for the entire period of the fact table. Also as said before, the Date format of fact table is 'yyyy-mm-dd hh:mm:ss.ssss', and the autoCalendar date selection format is 'dd/mm/yyyy' .

Thanks in advance.

[isorinrusu]

Hi,

Please copy the expression I wrote instead of the one you're naming as "YTD New", since this is wrong:

DateNum={"=$(vYearStartNum)<=$(vMaxDateNum)"}

Test it, and if the result is not correct, please put the variables $(vYearStartNum) and $(vMaxDateNum) in two different KPIs and convert them to date if necessary, just to see if the dates are calculated correctly.

[FlAsh92]

Hey @isorinrusu 

Thanks for the reply!!

But the expression doesn't seem to work either, attaching below:

Sum({<[T_Date.autoCalendar.Year]=, [T_Date.autoCalendar.Month]=, [T_Date.autoCalendar.Quarter]=, [T_Date.autoCalendar.Week]=, [T_Date.autoCalendar.Date]=, DateNum={"=$(vYearStartNum)<=$(vMaxDateNum)"}>} T_Value)

Additionally I set the variable in KPIs too, and the result is attached in the screenshots. Not sure if my code was right, but I tried converting it to Date format too.

YearStartNum:

Date(vYearStartNum)

MaxDateNum:

Date(Date#(vMaxDateNum))

Both the outputs of KPIs are just '-', while the YTD output is the same as before. Screenshot attached below.

Sorry for the delayed reply, and thanks in advance!

[sunny_talwar]

Looking at the expression alone doesn't seem to show that you are doing anything incorrect. Is it possible for you to share a sample to show the issue of incorrect numbers that you are seeing.

[FlAsh92]

Hello @sunny_talwar !!

Thanks for the reply. I currently do not have a QMC installed, will share the same as soon as it is done.

[FlAsh92]

Hello @sunny_talwar  @isorinrusu , sorry for the delayed reply.

I'm attaching the .qvf file, a screenshot of my excel file(unable to attach it) and also a screenshot of my test run.

Please help me on this, I've been trying to sort this out since a long time by myself.

[sunny_talwar]

Try this

Sum({<[DateField.autoCalendar.Year],
	  [DateField.autoCalendar.Month],
      [DateField.autoCalendar.Quarter],
      [DateField.autoCalendar.Week],
      [DateField.autoCalendar.Date],
      DateField={">=$(vYearStartNum)<=$(=vMaxDateNum)"}>}
Value)

While also changing your vYearStartNum to

=TimeStamp(YearStart(Max([DateField.autoCalendar.Date])))

[FlAsh92]

@sunny_talwar  Thanks for the reply!!

I downloaded the app and checked it. But the YTD function doesn't seem to work right.

For example: If I make a selection in the date filter as 02/02/19, then the Value should be summed from 01/01/2019 upto 02/02/2019. But that doesn't seem to be the case, as it sums up the whole year. 

That is not how I want my YTD to work. Please suggest a solution.