
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
sum of items more than 200 for 2 consecutive working days in a month- how to highlight
As per below screenshot I want to highlight sum of items more than 200 on given date for 2 consecutive working days in a month, please help
- « Previous Replies
-
- 1
- 2
- Next Replies »
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Ok i understood miscorrectly. So if the first day is to be included in the highlighting of a 2-day in a row then this:
=If(Sum({<[SLA] = {'24','48','72'}>} [UKI_ES_Items] ) >=200
and (
above(Sum({<[UKI_ES_Opened.autoCalendar.Date]=, [SLA] = {'24','48','72'}>} [UKI_ES_Items] ),1) >= 200
or
above(Sum({<[UKI_ES_Opened.autoCalendar.Date]=, [SLA] = {'24','48','72'}>} [UKI_ES_Items] ),-1) >= 200
)
, 'Red')

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Try below. Update Sum( [items] ) with your actual measure expression and [date_field] by your x-axis date dimension field name.
If(Sum( [items] ) >200 and above(Sum({<[date_field]=>} [items] ),1) > 200, red())

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
hi Thanks for you reply, its working but somehow it catching values below 200 , please see below
Dimention "= UKI_ES_Opened.autoCalendar.Date"
Measure "= sum({$<[UKI_ES_Opened.autoCalendar.Year] = {'$(=year(today()))'},[UKI_ES_Opened.autoCalendar.Month] = {'$(=month(Today()))'}
,[SLA] = {'24','48','72'}>}UKI_ES_Items)
Color Expression=If(Sum( [UKI_ES_Items] ) >=200 and above(Sum({<[UKI_ES_Opened.autoCalendar.Date]=>} [UKI_ES_Items] ),-1) >= 200, 'Red')
can you see 2nd bar, also I want Friday and Monday should be considered as consecutive

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Likely it's the SLA set analysis that's not in it. I would use the entire measure's set anslysis in your color expression, but you can at least try this:
=If(Sum({<[SLA] = {'24','48','72'}>} [UKI_ES_Items] ) >=200 and above(Sum({<[UKI_ES_Opened.autoCalendar.Date]=, [SLA] = {'24','48','72'}>} [UKI_ES_Items] ),-1) >= 200, 'Red')

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I have used .......=If(Sum({<[SLA] = {'24','48','72'}>} [UKI_ES_Items] ) >=200 and above(Sum({<[UKI_ES_Opened.autoCalendar.Date]=, [SLA] = {'24','48','72'}>} [UKI_ES_Items] ),-1) >= 200, 'Red')........this in color expression but now it skipping bars which are consecutively more than 200

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Oh sorry i put -1 in my above offset. That should be 1 to look at previous record, right now it's looking at next date...
=If(Sum({<[SLA] = {'24','48','72'}>} [UKI_ES_Items] ) >=200 and above(Sum({<[UKI_ES_Opened.autoCalendar.Date]=, [SLA] = {'24','48','72'}>} [UKI_ES_Items] ),1) >= 200, 'Red')

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Now after puting '1' instead of '-1' is showing as below, skipping bar of 6th and 13th sep.
Also I tried removing 1 but no change

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Ok i understood miscorrectly. So if the first day is to be included in the highlighting of a 2-day in a row then this:
=If(Sum({<[SLA] = {'24','48','72'}>} [UKI_ES_Items] ) >=200
and (
above(Sum({<[UKI_ES_Opened.autoCalendar.Date]=, [SLA] = {'24','48','72'}>} [UKI_ES_Items] ),1) >= 200
or
above(Sum({<[UKI_ES_Opened.autoCalendar.Date]=, [SLA] = {'24','48','72'}>} [UKI_ES_Items] ),-1) >= 200
)
, 'Red')

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks, But still it skipping last bar which is above 200 and has previous consecutive bar as 200+

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
It should work. Add those 3 expressions (current date measure, previous day, next day) in a straight table and make sure they are matching the graph. I expect it's off and is because of a different in the set analysis. The set analysis should be consistent between them.

- « Previous Replies
-
- 1
- 2
- Next Replies »