Count entries prior to a certain date

[HITECHFS]

Hi All,

I'm sure part of this is very simply but I just can't find a working solution. I have scoured the forum looking at similar situations but can't get other solutions to work. Can someone please help?

Using the below table, I want a bar chart where the bars are "Location" and the measure is a count of "DateIn" entries prior to a certain date. 

E.g. If the 'prior to' date was 01/01/2012, Location 3300 count would be 1 and Location 3315 count would be 2.

I've been trying to use the measure expression: =count({<DateIn={'<=01/01/2012'}>} DateIn) but it doesn't work.

I also have to run this report each week where the 'prior to' date changes. I.e. next week I may be looking for a count prior to 01/01/2013. Rather than come in and change the date in the expression each week, is there any way to have separate, editable chart/box/something that I can just change the date in?

I know I am asking a lot, if you read this far, thank you for your time!

Location	DateIn
3300	31/10/2011 09:50:21
3315	05/11/2011 09:54:45
3315	31/12/2011 14:01:31
3315	07/11/2013 14:01:55

[MayilVahanan · Accepted solution]

HI @HITECHFS 

Try like below

SET TimestampFormat='DD/MM/YYYY h:mm:ss';

LOAD *, Date(DateIn) as Date INLINE [
Location, DateIn
3300, 31/10/2011 09:50:21
3315, 05/11/2011 09:54:45
3315, 31/12/2011 14:01:31
3315, 07/11/2013 14:01:55
];

IN expression, try like below

=count({<Date={'<=01/01/2012'}>} DateIn)

 I.e. next week I may be looking for a count prior to 01/01/2013.

<-- is user input or any logic behind?