Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
March 11, 2PM EST: Do More with Qlik - Getting Started wtih Qlik Sense SaaS - Redux. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
FlAsh92
Contributor III
Contributor III

Possible Date Format Issue in YTD

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.

Labels (3)
1 Solution

Accepted Solutions
sunny_talwar

Change your variables to the ones in the image and that seems to make them work

image.png

View solution in original post

13 Replies
isorinrusu
Partner
Partner

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
Contributor III
Contributor III

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

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
Contributor III
Contributor III

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
Contributor III
Contributor III

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
Contributor III
Contributor III

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.YTD Error-3.pngYTD Error-4.png

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
Contributor III
Contributor III

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