Any suggestions on how I can achieve a bar chart, where I have the 4 quartiles defined on the x-axis and number of records on the y depending which quartiles the Duration value falls under? My data is as follows -
Record,Month-Year,Duration(days),Status
1,Jan-17,200,Resolved
2,Feb-17,10,Resolved
3,Feb-17,40.22,Open
4,Mar-17,50.10,Open
I need a non-group'ed chart for specific month and a grouped chart for YTD months.
Count(if (Status = 'Resolved' and IsCompleteMonths_Resolve = '1' and IsInYTD_Resolve = '1' and DurationDays <= Fractile(total DurationDays,0.50),DurationDays))
The count of records is incorrect. I added an expression with the same condition to get the total number of records and it was correct. I then suspected maybe the fractile is getting calculated incorrectly. So i added the following expression:
if(Status = 'Resolved' and IsCompleteMonths_Resolve = '1' and IsInYTD_Resolve = '1', Fractile(total DurationDays,.50))
First of all, this returns no data. But when I remove Status condition, I get a value but it's incorrect. I am unable to figure out why adding Status as condition gives me no data. At complete loss here...
I was able to figure out the issues above. I am able to now get the correct fractile value. But the using TOTAL with fractile is breaking my chart, because it is disregarding my Month-Year dimension. So it ends up calculating the same median for all the months. And I can't count without using total with fractile. Anyone have any suggestions?
Count(if(DurationDays <= Fractile(total DurationDays,0.50),[Month-Year])) ---> This works but gives me wrong value
Count(if(DurationDays <= Fractile(DurationDays,0.50),[Month-Year])) --> Would like to do this but expression is incorrect.
I guess when using count with fractile, you need to use total.
