Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Guys,
I'm currently creating a Pivot table to show the results of multiple KPI's broken down by month.
My Rows are the KPI's themselves i.e.
Ref Name
---------------------------------------------------------------------------------
A1 Number of Referrals
A2 Number of Referrals seen within 5 working days
A3 Number of Urgent Referrals seen within 2 working Days
My Columns are the form submission months i.e.
2017 Sept 2017 Oct 2017 Nov
So I end up with the Pivot Table looking how I want it like this.....
For my measures, most of them work by doing an If statement for the formula to place the results next to a particular KPI:
I.e.
if(Ref='A3',count({$<[Condition] =
{"123"}>} [Submission Date]),
However, some of my formulas have additional if statements within them that I cant get to work such as....
if(Ref='A5',Sum(Aggr(if(NetWorkDays([Submission Date], "Start Time", '25/12/2017','26/12/2017','01/01/2018','01/01/2018') <= 11, 1, 0),[Patient ID])))
It leaves the row blank, however if I place this formula in the first row i.e.
if(Ref='A1',Sum(Aggr(if(NetWorkDays([Submission Date], "Start Time", '25/12/2017','26/12/2017','01/01/2018','01/01/2018') <= 11, 1, 0),[Patient ID])))
it works correctly.
Any help on this would be much appreciated,
Really need help on this one for a current project
Try with NODISTINCT after Aggr()
If(Ref = 'A5', Sum(Aggr(NODISTINCT If(NetWorkDays([Submission Date], "Start Time", '25/12/2017', '26/12/2017', '01/01/2018', '01/01/2018') <= 11, 1, 0), [Patient ID])))
Still getting all blanks unfortunately, is there any other reason this might not be working correctly?
Would you be able to share a sample to show the issue?
Create a sample app and share with us