count the number of times it fall in the (mean+ standard deviation) range

[jimmygohhanjie]

hi,

I have 65 teams in Monthly dimension (YTD 2019) in a pivot table.

want compute the number of times the 'VOLUME IN' is within the team's monthly Standard deviation range.

But i am not able to do so dynamically, across the teams and Monthly. please see attached photo

If it is 1 team in a month. I can put the MEAN and STDEV in a fixed variable. but is not expected outcome

set anaylsis.

daily Volume in = Concat(aggr(num(sum({$<DATASOURCE_ID={'DAILY ALLOCATION'},METRIC_LEVEL1_ID={'Volume In','VOLUME IN'},TAG-={'SHARED'},WORKING_DAYS={'1'}>}EFFORT)/60,'###.#'),TEAM_ID,MTK_DATE ), ' | ')

 STDEV = Stdev(aggr(sum({$<DATASOURCE_ID={'DAILY ALLOCATION'},METRIC_LEVEL1_ID={'Volume In','VOLUME IN'},TAG-={'SHARED'},WORKING_DAYS={'1'}>}EFFORT)/60,TEAM_ID,MTK_DATE ))

mean = Avg(aggr(sum({$<DATASOURCE_ID={'DAILY ALLOCATION'},METRIC_LEVEL1_ID={'Volume In','VOLUME IN'},TAG-={'SHARED'},WORKING_DAYS={'1'}>}EFFORT)/60,TEAM_ID,MTK_DATE )) // mean

count of days in a month within STDEV = ???

please see attached photo for the desired.

I am not able to attached the qvw as is confidential.

 thanks in advance

[jimmygohhanjie]

@sunny_talwar  could you kindly assist?

or if this is possible in qlikivew

[sunny_talwar]

May be this for Count of days in month within Stdev

=Sum(Aggr(
If(Sum({$<DATASOURCE_ID = {'DAILY ALLOCATION'}, METRIC_LEVEL1_ID = {'Volume In', 'VOLUME IN'}, TAG -= {'SHARED'}, WORKING_DAYS = {'1'}>} EFFORT)/60 <= Stdev(TOTAL <TEAM_ID, MTK_MONTH_YEAR> Aggr(Sum({$<DATASOURCE_ID = {'DAILY ALLOCATION'}, METRIC_LEVEL1_ID = {'Volume In', 'VOLUME IN'}, TAG -= {'SHARED'}, WORKING_DAYS = {'1'}>} EFFORT)/60, TEAM_ID, MTK_MONTH_YEAR, MTK_DATE)), 1, 0)
, TEAM_ID, MTK_MONTH_YEAR, MTK_DATE))

[Brett_Bleess]

Did Sunny's reply help, or are you still looking for help?  If the reply did work, please give Sunny credit by clicking the Accept as Solution button on his post, and if you still need further help, provide an update on where you stand.  If you figured it out on your own, please share what you did, so others may learn, you can mark your own reply as the solution as well in that case.

Regards,
Brett