Hi, I have been trying to create line chart showing a count of the number of items of a particular status on a given day. I have been using rangecount and rangesum to try to achieve this with no luck.
Basically, i have a set of items (Id) which have a StatusCode (could be either A, C, U F...) . The items could have a 'NULL' or '00:00:00' value for TDate and also have a date created (%_Date)
| Id | StatusCode | TDate | %_Date |
| 130 | F | 00:00.0 | 4/06/2015 |
| 149 | F | NULL | 5/06/2015 |
| 173 | F | 00:00.0 | 5/06/2015 |
| 185 | F | 00:00.0 | 5/06/2015 |
| 237 | F | NULL | 8/06/2015 |
| … | … | … | … |
| 8006 | F | NULL | 18/01/2016 |
| 8065 | F | NULL | 19/01/2016 |
| 8071 | F | NULL | 19/01/2016 |
I would like to count the number of unique items (Id) with an F StatusCode which were created (%_Date) on the day with a NULL TDate, for the last 30 days only
I have been using the expressions as follows;
Dimension:
if((Today()-[xxx.%_Date])<=30, [xxx.%_Date])
Measure:
RangeCount (Above(Count(Distinct if(xxx.StatusCode='F'and IsNull(xxx.TDate), [xxx.Id])),0,rowno()))
I am currently having two issues depending on which calcs i use. Either the date values are now unique, thus i will have two or three of the same date with one item attributed to each, OR i the graph will display a correct incline BUT the first date will have a value of one where it should actually have a value of 40 and accumulate from there.
Any help would e greatly appreciated.
thanks
Michael
