Filter by Date


https://www.nevron.com/Forum/Topic5031.aspx
Print Topic | Close Window

By Jonathan Green - 13 Years Ago
I have the following sample data from SQL:

SymDate CloseVal VolumeVal
27/04/2011 00:00:00 124 10404
26/04/2011 00:00:00 124.14 137740
25/04/2011 00:00:00 123.66 68777
21/04/2011 00:00:00 123.99 125848
20/04/2011 00:00:00 123.85 159345
19/04/2011 00:00:00 121.33 218996
18/04/2011 00:00:00 121.61 153145
15/04/2011 00:00:00 123.45 136575
14/04/2011 00:00:00 122.36 24947
13/04/2011 00:00:00 122.88 117004
12/04/2011 00:00:00 120.92 200767
11/04/2011 00:00:00 123.98 165617
08/04/2011 00:00:00 126.65 143676
07/04/2011 00:00:00 122.67 146927
06/04/2011 00:00:00 122.3 151058
05/04/2011 00:00:00 122.22 192926
04/04/2011 00:00:00 121.06 154770
01/04/2011 00:00:00 118.7 177493
31/03/2011 00:00:00 117.36 170417
30/03/2011 00:00:00 115.13 116598
29/03/2011 00:00:00 115.16 149041

I want to display all of the data but filter the symdate to only display labels for each Friday on the X axis with he following format =FORMAT(DATETIME(Fields!SymDate.Value),"d","en-GB").

I have tried the following with no luck =Fields!SymDate(WEEKDAY(DATETIME("16/03/2011"))) under Axes/Scale /Label Format, resulting in all labels as 3 (Wednesday)

Any pointers on how to achieve this would be greatly appreciated.




By Nevron Support - 13 Years Ago

Hello Jonathan,
If we can understand your requirement correctly, here is the solution for displaying all the data and having labels on the X axis only for the Fridays:

1. In the Data Groupings >> Categories >> General, we have 3 expressions for Group By:
=YEAR(Fields!SymDate)
=MONTH(Fields!SymDate)
=DAY(Fields!SymDate)

For the Label we have:
=IF( WEEKDAY(FIRST(Fields!SymDate))=5, FORMAT(FIRST(Fields!SymDate), "d", "en-GB"), "")

2. In the Data Groupings >> Categories >> Sorting, we have 3 expressions:
=YEAR(Fields!SymDate)
=MONTH(Fields!SymDate)
=DAY(Fields!SymDate)

3. In the Data Groupings >> Values, we have two values for CloseVal and VolumeVal:
=SUM(Fields!CloseVal)
=SUM(Fields!VolumeVal)

For the second value, from General >> Options, we have selected Display on Axes – Y Axes: Secondary Y

4. In the Chart >> Axes, select Secondary Y and go to the Range tab. Set Inflate Mode to Tick and uncheck the Inflate Range Begin

For this example we have used secondary Y axis for better readability because there is great difference in the values (Close Val and Volume Val).