topic Re: If Statement in Pivot Table in App Development

If Statement in Pivot Table

richardm90 — Fri, 01 Jun 2018 14:38:22 GMT

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,

Re: If Statement in Pivot Table

richardm90 — Sun, 03 Jun 2018 10:36:54 GMT

Really need help on this one for a current project

Re: If Statement in Pivot Table

sunny_talwar — Sun, 03 Jun 2018 13:48:36 GMT

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

Re: If Statement in Pivot Table

richardm90 — Sun, 03 Jun 2018 23:26:50 GMT

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

Re: If Statement in Pivot Table

sunny_talwar — Mon, 04 Jun 2018 00:23:01 GMT

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

Re: If Statement in Pivot Table

avinashelite — Mon, 04 Jun 2018 05:54:56 GMT

Create a sample app and share with us