Sum a flag count

[MEBG93]

Hello everyone!

I need to sum a flag count. The query goes like this:

if(

avg({<=Date(Max({<[DATE]=p(DATE)>}[DATE]),'YYYY-MMM'),group= {'AA'}>}value)

>(Avg(total value)+ 2*(StDev(total value))),

1,0)

This shows me either 1 for values over  +2 stdev for value or 0 for the rest. I need to sum those counts in a KPI chart. I've tried:

if(

avg({<=Date(Max({<[DATE]=p(DATE)>}[DATE]),'YYYY-MMM'),group= {'AA'}>}value)

>(Avg(total value)+ 2*(StDev(total value))),

sum(avg({<=Date(Max({<[DATE]=p(DATE)>}[DATE]),'YYYY-MMM'),group = {'AA'}>}value)))

with no success.

😞

[Kashyap_R]

Hi 

Try like this

Sum(

if(

avg({<=Date(Max({<[DATE]=p(DATE)>}[DATE]),'YYYY-MMM'),group= {'AA'}>}value)

>(Avg(total value)+ 2*(StDev(total value))),

1,0)

)

Hope this helps 

Thanks

[sunny_talwar]

What dimension are you testing this against? Try this

Sum(Aggr(
If(Avg({<[DATE] = {"$(=Date(Max({<[DATE]=p(DATE)>}[DATE]), 'YYYY-MMM'))"}, group = {'AA'}>} value) > (Avg(TOTAL value)+ 2*(StDev(TOTAL value))),
1, 0)
, Dimension/s))

[MEBG93]

Thanks for your responses. The dimension being tested is product_name.

I want to count values (or ultimately product_name as being the most detailed dimension) that are over +2 standard deviation of the avg value in a KPI Chart. So if I insert Sunny's suggestion in a table, works; but in a KPI Chart the result is zero (or false in the if statement).

This is because the first statement is calculated as a whole value and not by different values detailed by its dimensions (date, table, name, product_name, in that order)

[sunny_talwar]

So, all of these are you dimensions in the chart?

date, table, name, product_name

If they are, you are saying that this works in a chart but not in a KPI?

Sum(Aggr(
If(Avg({<[DATE] = {"$(=Date(Max({<[DATE]=p(DATE)>}[DATE]), 'YYYY-MMM'))"}, group = {'AA'}>} value) > (Avg(TOTAL value)+ 2*(StDev(TOTAL value))),
1, 0)
, date, table, name, product_name))

Would you be able to share a sample where we can see the issue?

[MEBG93]

Those dimensions are in a table, and they are needed to calculate the flag. Something like the table attached.

So what I need to do is to count that flag into a KPI Chart.

If I traspose the flag expression into the KPI Chart it calculates the entire universe vs. the total value. Even if the dimensions are properly called.

[sunny_talwar]

I understand that... but have you tried the above expression and it didn't work?

[MEBG93]

No, because in 

Avg({<[DATE] = {"$(=Date(Max({<[DATE]=p(DATE)>}[DATE]), 'YYYY-MMM'))"}, group = {'AA'}>} value)

shows up the avg of the whole universe, and if I  add: Avg({<[DATE] = {"$(=Date(Max({<[DATE]=p(DATE)>}[DATE), 'YYYY-MMM'))"}, group = {'aa'}, table, name, product_name>} value) 

it doesnt sums the flag value

[sunny_talwar]

If Avg() is within Aggr() function... why do you think it average everything? It should average by each dimension in your expression.... I think you might have been confused... If you are looking to get 2 from the above image, I would think that the expression should work.

I am sorry, but I won't be able to provide much more help without seeing the app where you have the issue if you still continue to have issues.

Best,
Sunny