Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hiya
how do you create a filter pane to just show AM and PM option
Time([Order Date],'TT')
this works , but It creates loads of instances of AM and PM.
need to group by AM and PM.
I have done this in the data load too
Date(Floor([Order Date])) as "Order Date",
please help
I think that AND should be an OR. I don't think you're expecting an Order Date which is before 8am *and* after 5pm.
I'd think you want;
If(((Frac([Order Date]))>=(17/24) OR (Frac([Order Date]))<=(8/24)), 'PM', 'AM') as Night and Day
Hiya
date and time is held in same field
tried this too but no joy
If((Time(Frac([Order Date]))>=Time('12:00:00') AND Time(Frac([OrderDate]))<=Time('00:00:00')), 'PM', 'AM')
Please help
That's because Time() simply formats the value, the underlying value is not changed, so each distinct value will appear in the list box even though they look the same due to the formatting.
You need to create two distinct values, fort example:
=If(Frac([Order Date]) >= 0.5, 'PM', 'AM')
Better still is to do this logic in the load scripts to create an AMPM field using the same logic.
hi
I have tried this in the load as suggested
as need to define AM and PM times
If(((Frac([Order Date]))>=Time('17:00:00') AND (Frac([Order Date]))<=Time('08:00:00')), 'PM', 'AM') as Night and Day
I still only see AM
Please help
try (17/24) instead of Time('17:00:00') and (8/24) instead of Time('08:00:00')
as Frac([Order Date]) will return a number, rather than a time.
If(((Frac([Order Date]))>=(17/24) AND (Frac([Order Date]))<=(8/24)), 'PM', 'AM') as Night and Day
oops - my bad
If(((Frac([Order Date]))>=(17/24) OR (Frac([Order Date]))<=(8/24)), 'PM', 'AM') as Night and Day
although:
If(((Hour([Order Date]))>=17 OR (Hour([Order Date]))<=8), 'PM', 'AM') as Night and Day
would probably be easier to read
Hiya
=If(Frac([Order Date]) >= 17/12, 'PM', 'AM')
works fine for first part
if I add next time bracket frame
If(((Frac([Order Date]))>=(17/24) AND (Frac([Order Date]))<=(8/24)), 'PM', 'AM')
I can only see AM
Please help
I think that AND should be an OR. I don't think you're expecting an Order Date which is before 8am *and* after 5pm.
I'd think you want;
If(((Frac([Order Date]))>=(17/24) OR (Frac([Order Date]))<=(8/24)), 'PM', 'AM') as Night and Day
Hiya
thanks team, we have a result.
thanks for both your help as Andrews answer worked to get AM / PM in general too.
wish you could check multiple correct instances
Kind Regards