Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
@sunny_talwar could you kindly assist?
or if this is possible in qlikivew
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))
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