count of sum of daily volume more than 500 is more than 5 days in month then highlight the value in heat map

[deeavhad]

hi , as per subject I need, Sum of item numbers on a given date for only SLA transactions. If the sum is greater than 500 on 5 days in a month, highlight the date 

I am trying following expression but it shows error

=If(count({<[UKI_ES_Opened.autoCalendar.Month]= (Sum({<[SLA] = {'24','48','72'}>} [UKI_ES_Items] )>500)>})>5, 'Red')

Please help

[rubenmarin]

Hi, you can try with:

If(Sum(Aggr(If(Count(distinct TOTAL <[UKI_ES_Opened.autoCalendar.Month]> {<DateField={"=Sum({<[SLA] = {'24','48','72'} [UKI_ES_Items])>500"},>} DateField)>=5,1,0),[UKI_ES_Opened.autoCalendar.Month],DateField)), Red())

[deeavhad]

Hi , I am trying to create HEAT MAP

Dimension 1- UKI_ES_Opened.autoCalendar.YearMonth

Dimension 2- =Day([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)+NullCount({$<[UKI_ES_Opened.autoCalendar.Year] = {'$(=year(today()))'},[UKI_ES_Opened.autoCalendar.Month] = {'$(=month(Today()))'},[SLA] = {'24','48','72'}>}UKI_ES_Items)

in options under appearance

Min scale = 500

Max Scale value- If(Sum(Aggr(If(Count(distinct TOTAL <[UKI_ES_Opened.autoCalendar.Month]> {<[UKI_ES_Opened.autoCalendar.Date]={"=Sum({<[SLA] = {'24','48','72'} [UKI_ES_Items])>500"},>} [UKI_ES_Opened.autoCalendar.Date])>=5,1,0),[UKI_ES_Opened.autoCalendar.Month],UKI_ES_Opened.autoCalendar.Date)), Red())

But chart is showing like this, as there is only one entry for above 500 still it highlighting, I need to highlight if on more than or equal to 5 days if sum of items is grater than 500 otherwise it should not highlight

Please Guide

[rubenmarin]

Hi ,if you are using UKI_ES_Opened.autoCalendar.YearMonth as dimension, use that field instead of [UKI_ES_Opened.autoCalendar.Month], something like:

 If(Sum(Aggr(If(Count(distinct TOTAL <[UKI_ES_Opened.autoCalendar.YearMonth]> {<[UKI_ES_Opened.autoCalendar.Date]={"=Sum({<[SLA] = {'24','48','72'} [UKI_ES_Items])>500"},>} [UKI_ES_Opened.autoCalendar.Date])>=5,1,0),[UKI_ES_Opened.autoCalendar.YearMonth],UKI_ES_Opened.autoCalendar.Date)), Red())

If doent' works it would be great if you can uplaod a sample to make some tests.

You can add fixed data susing inline load just to demonstrate the issue

[deeavhad]

Hi I have attached the Data and I need only current month view, so the measure is like above and also guide me where to use the expression provided by you for heat map

[rubenmarin · Accepted solution]

Ther is no month with those SLA that meets the requirements, if you add NA and TBD this works:

If(Sum(Aggr(If(Count(distinct TOTAL <UKI_ES_Opened.autoCalendar.YearMonth>
{<[UKI_ES_Opened.autoCalendar.Date]={"=Sum({<[SLA] = {'24','48','72','NA','TBD'}>} [UKI_ES_Items])>500"}>} [UKI_ES_Opened.autoCalendar.Date])>=5,1,0)
,UKI_ES_Opened.autoCalendar.YearMonth,UKI_ES_Opened.autoCalendar.Date)), Red())

[deeavhad]

Hi Rubenmarin,

Thanks for your help it works

I have another requirement with the same data set I have shared with you and same chart, now I want that sum of items for 2 consecutive days is more than 200 then it should be highlighted for e.g. if on 1st day sum is 201 and on 2nd day sum is 250 then that should be highlighted, also if on a day sum is more than 200 and consecutive day has sum less than 200 then it should not be highlighted