Check for a certain timestamp at max date

[ioannagr]

Hello.

I want in my data to check the following thing:

If maxdate < 10 am of the maxdate, then print maxdate, else maxdate-1

Is that possible?

Also how can I do this on the backend? With resident load?

[Vegar]

Assuming that by maxdate you mean max(Date). I also assume that Date is a field containing a timestamp (date and time).

If so you could do use an expression like below:

=Dayname(Max(Date-maketime(10)) )

[ioannagr]

hi @Vegar  look what i get by this

i would like the right column to show  max(date) 10:00:00.00000 

so for this case, 22/9/2020 10:00:00.00000 since 22/9 happens to be the max date.

Any ideas?

[ioannagr · Accepted solution]

I found it , I did

=IF(date(Max(date,'DD/MM/YYYY hh:mm:ss.ffffff')<date(Max([date],
'DD/MM/YYYY 10:00:00.000000'),
date(Max([date),'DD/MM/YYYY'),date(Max([date-1),'DD/MM/YYYY'))

and works fine!

[ioannagr]

@Vegar  hello,  i have a calculated dimension like that 

=aggr(if(aggr(Sum({<[date]= {'$(=Date(max([date]-1),'DD/MM/YYYY'))'},[availability]={'available'}>} [sales]),[client name])>0 or aggr(Sum({<[date]= {'$(=Date(max([date]-1),'DD/MM/YYYY'))'},[availability]={'inventory'}>} [sales]),[client name])>0, [client name]),[client name])

If i were to use your solution Dayname(Max(Date-maketime(10)) ) , how should i transform my above expression so as to only show the right clients?

[Vegar]

I am not sure that I follow you on the question, but you need to make your modifier for [date] generate your desired output formated in the same way as [date] is formated. So if you currently are using  [date]= {'$(=Date(max([date]-1),'DD/MM/YYYY'))'} and want to change this to the 10 o clock logic you might want to do the modifier like this? [date]= {'$(=Dayname(max([date]-maketime(10))))'} but you don't need to use dayname(), I think date() will also do the trick like this: [date]= {'$(=Date(max([date]-maketime(10)),'DD/MM/YYYY'))'}

I hope this comment could be of some help for you.

BR Vegar

[ioannagr]

Hi again @Vegar .

I think you can help me.

See, if the max date timestamp is say , 10/11/2020 07:59:00.000000 then my IF expression in the initial post i did,  will assign this client to the previous date  09/11/2020 because 10/11/2020 08:00:00.000000 >10/11/2020 07:59:00.000000 . The comparison happens in the IF expression right?

I have a list of clients each with their own date timestamp.

For my example,in my calculated dimension, I want to print only those  clients that have data for 09/11/2020.

Your suggestion prints various dates

[ioannagr]

@sunny_talwar  @Kushal_Chawda  sorry for directly referencing you, but can you also kindly look into this? 

You have been of so much help so far to me, so maybe this time too ? 😄

I want to show those clients that their date(max(timestamp -maketime(10), 'DD/MM/YYYY')) is equal to 5/11/2020, while the max date there can be found in the data is 6/11/2020.

[Vegar]

I do not completely understand you @ioannagr . The Dollar expansion for the set modifier {'$(=Dayname(max([date]-maketime(10))))'} will be evaluated outside of the chart and all rows in your chart will be evaluated against the same Date value, it will not differ per row. It could however differ if you make other selections that indirect changes the available dates in your selection.  To ignore selections you could define a set for the max() function as well, e.g. letting the date ignore all selections like this:{'$(=Dayname(max({1}[date]-maketime(10))))'} 

[ioannagr]

Okay let me describe to you what I want to do. The system gets updated say at midnight. So, even though some departments insert the new records all before 12 am, I get to see them the day I open the app. If they do later in the day, I don't see them, but i do see them the day after if I select in my filter the previous order date. To avoid this, i've thought of doing this dayname(max(orderdate)-maketime(10))) you suggested, so as to if they do the insertion after 10 am I count them in the max(orderdate), else I assign them to the previous date.

In this table, I want to show, for this dayname date, a calculated dimension for the clients that have an order on this date.

Before, my client calculated dimension was :

=aggr(if(aggr(Sum({<[date]= {'$(=Date(max([date]-1),'DD/MM/YYYY'))'},[product availability]={'available'}>} [sales]),[client name])>0 or aggr(Sum({<[date]= {'$(=Date(max([date]-1),'DD/MM/YYYY'))'},[product availability]={'inventory'}>} [sales]),[client name])>0, [client name]),[client name])

Now i want to incorporate my dayname change to this above expression to show the correct clients (which if i just copy and paste does not work, looks like it doesnt take into account the change). Note that also my initial date filter does not sychronise with this dayname, I tried this expression in filter pane =Num([orderdate timestamp])=Num(Max(ALL Date(Max([orderdate timestamp]-maketime(08)),'DD/MM/YYYY'))) also doesn't work. I thought of making a bookmark for this page so as to get only , if today was 6/11/2020, the dayname(max([date]-maketime(10))) would give me the correct ones, but i don't know.

Please provide me with suggestions, you must know better than me!

I'm lost and i don't know what to do  😞

@Vegar