Make week from a Floor(Date)

[igor_leblanc]

Hi Everyone 🙂

I'm new to Qlik and I struggle with some simple matters.

I transformed a date which was DD/MM/YYYY hh:mm into DD/MM/YYYY with floor formula : 

Date(Floor( Timestamp#( "Date Initialisation",'DD/MM/YYYY hh:mm')),'DD/MM/YYYY') as "Date Initialisation",

Now i would like to transform this date in Weeks. I can do it with normal dates with that formula : 

if(year("Date Reception BC") like '2020','0', Week("Date Reception BC"))as [Date Reception BC Semaine],

But this formula does not work with my floor(date)

And yes I need to keep just the date from the current year, that is what I found to do so. If you have simpler, do not hesitate 😉

How could I do ?

Regards, 

[sunny_talwar]

The date field you transformed is called Date Initialisation, what is Date Reception BC? Is it another transformed field? If it is transformed correctly, you can try this

If(Year("Date Reception BC") = Year(Today(1), Week("Date Reception BC")) as [Date Reception BC Semaine]

[igor_leblanc]

Hello, 

Unfortunately, I only get a blank as below 

I think my date is fine though, see below : 

Date Reception BC is not a transformed field, that's why it is working. With the transformed one, it does not work 😞

any idea ?

[igor_leblanc]

I got this for the moment : 

Date(Floor( Timestamp#( "Date Initialisation",'DD/MM/YYYY hh:mm')),'DD/MM/YYYY') as "Date Initialisation",

If(Year("Date Initialisation") = Year(Today(1)), Week("Date Initialisation")) as [Date Init Semaine],

[sunny_talwar · Accepted solution]

Try this

Date(Floor( Timestamp#( "Date Initialisation",'DD/MM/YYYY hh:mm')),'DD/MM/YYYY') as "Date Initialisation",

If(Year(Timestamp#("Date Initialisation", 'DD/MM/YYYY hh:mm')) = Year(Today(1)), Week(Timestamp#( "Date Initialisation",'DD/MM/YYYY hh:mm'))) as [Date Init Semaine],

[igor_leblanc]

You're a genius !!

How could I do if I wanted to have only the last 6 month ?

Thanks again, 

Igor

[igor_leblanc]

Sorry, let me be more precise !

I would like to have this information on a graph like this : 

But only for last 6 month. How to do so ? 

Many thanks, 

Igor

[sunny_talwar]

lol thanks. Sorry, I am not sure I understand your follow up question? Are you saying you want to create a flag which identify last 6 months from today or another date?? or do you want to restrict your dashboard to just last 6 months from today or another date??

[igor_leblanc]

I would like to restrict my dashboard to last 6 month from today 🙂

[sunny_talwar]

Add a where statement to your table load

Where Timestamp#("Date Initialisation", 'DD/MM/YYYY hh:mm') >= AddMonths(Today(1), -6)