If Statement in Pivot Table

[richardm90]

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,

[richardm90]

Really need help on this one for a current project

[sunny_talwar]

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])))

[richardm90]

Still getting all blanks unfortunately, is there any other reason this might not be working correctly?

[sunny_talwar]

Would you be able to share a sample to show the issue?

[avinashelite]

Create a sample app and share with us