Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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?
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?