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, 

[igor_leblanc]

I am not sure to understand. Where shoudl it be put ? what is the table load ? I only want it for this particular graph

[igor_leblanc]

I am not sure to understand well. Where should it be put ? I want it only for this particular graph

[sunny_talwar]

I thought you wanted to do it for the whole dashboard, if you want to do it for a specific chart, then create a flag for this in the script

If(Timestamp#("Date Initialisation", 'DD/MM/YYYY hh:mm') >= AddMonths(Today(1), -6), 1, 0) as [Last6MonthFlag],

and then use this in your expression

{<[Last6MonthFlag] = {'1'}>}

[igor_leblanc]

For now my expression is just =[Date Init Semaine]
How can I implement yours ?
Regards

[sunny_talwar]

This

=Only({<[Last6MonthFlag] = {'1'}>} [Date Init Semaine])

[igor_leblanc]

It does not work unfortunately. I have put your formula in the load editor as below :
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],
If(Timestamp#("Date Initialisation", 'DD/MM/YYYY hh:mm') >= AddMonths(Today(1), -6), 1, 0) as [Last6MonthFlag],

And this on the graph :

=Only({<[Last6MonthFlag] = {'1'}>} [Date Init Semaine])

And I have :

[cid:image001.png@01D7A980.1C8D4F90]

Any idea ?

[sunny_talwar]

I can't see the image that you posted. but were you using [Date Init Semaine] as a dimension or measure in your chart?

[igor_leblanc]

I use it as a dimension. Should I use it as a measure ?
The image shows "Invalid dimension"
Regards,

[sunny_talwar]

In that case, try this

If([Last6MonthFlag] = 1, [Date Init Semaine]))

and you can suppress null values for your dimension to hide all nulls

[igor_leblanc]

So great, it works perfectly fine now.

I have one last question about this modified dates. I would need to create a graph as below : 

So compare a month from year 1 to same month to year 2. How could I do it ? I imagined that I could create new fields like "Date Reception BC month 2020" and "Date Reception BC month 2021" and so on... But I can not find the right way to write the formula. And I do not know if they would be compared the right way.

Any ideas ?

Greetings, 

Igor