6 Replies Latest reply on Feb 20, 2017 9:18 PM by Shinichiro Murakami

Need help with level of detail and stack bar chart

Hi have requirement to get number of calls that are above 7 day average for each ACTIVITY and for DATE selected. below is how the data set looks like. count(ucid) is considered as number of calls.

I have calculated 7 day average for each activity as below. 7 day Avg calculation is sum of duration for last 7 days / total number of calls for last 7 days(do not compare avg against sample data set since it is dummy one)

Now I need a stacked bar chart with activity in x axis(for any chosen DATE), The stacks(categories) I need are:

1. Number of calls where call duration was > 7 day Avg

2. Number of calls where call duration was <= 7 day avg

• 1. Re: Need help with level of detail and stack bar chart

Please share the sample workbook, Will try to help.

Thanks

• 2. Re: Need help with level of detail and stack bar chart

I have attached the workbook. In the app usage tab, am displaying 7 day avg as reference line against the 1 day avg. So basically 102.6 is 7 day avg and 81.5 is the 1 day avg for iexplore-External activity. If you hover over iexplore external bar, you can see duration and calls. So out of 55 calls, i need to know how many calls are above 7 day avg and how many are below. Once we get that, i need to have bar converted into stack bar which consists of no. of calls above and below 7 day avg. Is this possible?

If not, in sheet 37 i just have activity against total calls. Can we get no. of calls above and below 7 day avg there in form of stack ?

• 4. Re: Need help with level of detail and stack bar chart

Hi Monoj

Thank you for the reminder.

I'm not sure I understand your request correctly, but simply like below?

[Color 7 days]

if [duration]<=7 then "Within 7" else "More than 7" end

Thanks,

Shin

• 5. Re: Need help with level of detail and stack bar chart

Hi Shin - Thanks for reply. I tried above calculation above but it did not work. Because am comparing it against [7 day avg] measure and not just the value 7. Even if it try to enclose [Duration] measure within attr() function, it converts the calculated field to Measure and I cannot ctrl drag and drop drv_Activity and the new calculation to color pill. Let me know if am wrong?

• 6. Re: Need help with level of detail and stack bar chart

Maybe this?

[7 Days Duration 2]

{fixed [drv_Activity]:sum(if (datediff('day', [ActivityDate], [Parameters].[ActivityDate])>=0) and (datediff('day',[ActivityDate], [Parameters].[ActivityDate])<=7) then [duration] END)}

[7 Days Ucid 2]

{fixed [drv_Activity]:countd(if (datediff('day', [ActivityDate], [Parameters].[ActivityDate])>=0) and (datediff('day',[ActivityDate], [Parameters].[ActivityDate])<=7) then [ucid] END)}

[7 Days Avg 2]

[7 Days Duration 2]/[7 Days Ucid 2]

[Above / Below]

if [7 Days Avg 2]>=[duration] then "Above" else "Below" end

Thanks,

Shin