Month(today()) and Year(Today()) not providing the actual date

[Applicable88]

Hello,

All my expression function with a date function aren't working. I think there is a problem with either the date format or date related thing.  

One of the time related columns I have is the  Month-Year Data content looking like this:

Revenue Date
03-2020
03-2020
03-2020
03-2020
04-2020
04-2020
04-2020
04-2020
05-2020
05-2020
05-2020
05-2020

Since Qliksense didn't load it as a Date data, I change it to a "Date column" via Datamanager column options. In the autogenerated script I got the following confirmation, a line stating:

Date(Date#([Revenue Date],'MM-YYYY'),'MM-YYYY) As [Revenue Date]

So it now has a Date format. And also auto.calendar function. So far so good...

Since I don't have any correct data returned with Dollar Sign Expansion added Date functions like month(today()), year(today()) and so fourth. I tried to check the date function via KPI Window to verify this:

For Today() I get following Data:

For Year(today()) I get:

12.07.1905?  But the funny thing is when I put the same function for the title it returns the right year. 

Now for month(today()):

Again as KPI function I get wrong month but June is correct. 

I have to mention that my function using monthname function are returning correct.  Is there something I didn't put right?

Thanks in advance. 

[sunny_talwar]

I think what you are seeing is the date format in year. Year() function returns a number and not a date, but you are displaying it as a date. If you look 2020 in Excel and change it's format to date, you will see this 

You might just have to change the format of your KPI from date to number.

If the goal is to see 01/01/2020, then you can try using YearStart(Today())

[Applicable88]

Hi @sunny_talwar ,

thanks for the quick response. But Yearstart(today()) just returns "01.01.2020"

I actually want to return the month of today and year of today, through Month(today()) and year(today()).

For instance when I want to make a count with my date column for example:

count({$<Date={"$(=Month(Today())}>})sales

I get no value.

It still doesn't explains why both of the title of the KPI DO RETURN the right value with the same functions.

[sunny_talwar]

Do you have a Month field and Year field to compare the values too? Right now you are comparing Date to 2020 which would not return you anything... If you have Month and Year fields, you can try this

Count({$<Month ={"$(=Month(Today())}>} sales)
Count({$<Year ={"$(=Year(Today())}>} sales)

if you don't have those fields, you can try this

Count({$<Date ={">=$(=Date(MonthStart(Today())))<=$(=Date(Today()))"}>} sales)
Count({$<Date ={">=$(=Date(YearStart(Today())))<=$(=Date(Today()))"}>} sales)

[Dolphin]

I ran into a smilar problem. My Field looks like '30.09.2022'.

Taking the month of it strangely return 08.01.1900. The numeric value of the date 08.01.2022 is 9 (numeric value of September). 

Month(Fieldname) as Month

Settings are

SET MonthNames='Jan.;Feb.;März;Apr.;Mai;Juni;Juli;Aug.;Sep.;Okt.;Nov.;Dez.';

Using it in combination with the year function it returns the month as expected 2022 Sep.

Year(Fieldname) & ' ' & Month(Fieldname)

So for whatever reason it is taking the numeric value as standalone expression and turns it into a date. Explicitly calling the text value seems to resolve this issue, but I don't know how/why it created a date from the months numeric value. This also happens for different formats in the excel like text and custom.

Text(Month(Fieldname)) as Month

However this is limiting calculation as the numeric value will be missing.

Creating the Dual value manually does not work either, it still gives back the date of the months numeric value

Dual(Text(Month(Fieldname)), Num(Month(Fieldname)))

If someone has an idea why the month is returning the numeric value correctly but giving the date of it as text value pls let me know.

Solved:

Nevermind, it was the fields name as the field was called month. Renaming it to something else returns the expected values. Also using MonthName as a function seems to work, but that includes the year by default.