Row total Custamization

[neelamsaroha157]

Hi,

I have a Pivot table where I have to customize the Row level totals and the data is for rolling 12 months.

The expression I am using is:

if(IsNull(RowNo()) , 'n= ' & (Count(DISTINCT {<[Key Date Type] = {'DISCHARGE DATE'},Date ={">=$(vRolling12MinDate)<=$(vAsOfDate)"}, Year = , Month = , Quarter =>}SURVEY_ID)),

(Count({<[Key Date Type] = {'DISCHARGE DATE'},Date ={">=$(vRolling12MinDate)<=$(vAsOfDate)"}, Year = , Month = , Quarter =, ScoredStatus = {'1'}, CAHPS_Box_Type = {'TopBox'}>}RESPONSE)/

Count({<[Key Date Type] = {'DISCHARGE DATE'},Date ={">=$(vRolling12MinDate)<=$(vAsOfDate)"}, Year = , Month = , Quarter =, ScoredStatus = {'1'}, CAHPS_Box_Type = {'*'}>}RESPONSE)

)*100

)

The result is as per requirement BUT the only issue is its showing all other Month-Year(All data scrambled in image) that falls out of the condition with no data.

I can't figure out what I am doing wrong.

Would appreciate any help.

Thanks

[sinanozdemir]

Hi,

Maybe try Len(Trim(RowNo())) = 0.

I didn't think that RowNo() could be null, at least I haven't seen it yet.

[Jason_Michaelides]

I think you need to force your 'n=' to be zero so it's suppressed in the chart.  Try:

if(IsNull(RowNo()) , Dual('n= ',0) & (Count(DISTINCT...etc

Hope this helps,

Jason

[neelamsaroha157]

‌Hi Sinan,

Thanks for your reply.

For the grand total the rowno() is always null.

Thanks

[neelamsaroha157]

‌Thanks Jason,

I'll try it tomorrow and update you.

Appreciate your help.

[sunny_talwar · Accepted solution]

You might want to try this as well

If(IsNull(RowNo()) and

Count(DISTINCT {<[Key Date Type] = {'DISCHARGE DATE'},Date ={">=$(vRolling12MinDate)<=$(vAsOfDate)"}, Year = , Month = , Quarter =>}SURVEY_ID) > 0,

'n= ' & (Count(DISTINCT {<[Key Date Type] = {'DISCHARGE DATE'},Date ={">=$(vRolling12MinDate)<=$(vAsOfDate)"}, Year = , Month = , Quarter =>}SURVEY_ID)),

(Count({<[Key Date Type] = {'DISCHARGE DATE'},Date ={">=$(vRolling12MinDate)<=$(vAsOfDate)"}, Year = , Month = , Quarter =, ScoredStatus = {'1'}, CAHPS_Box_Type = {'TopBox'}>}RESPONSE)/

Count({<[Key Date Type] = {'DISCHARGE DATE'},Date ={">=$(vRolling12MinDate)<=$(vAsOfDate)"}, Year = , Month = , Quarter =, ScoredStatus = {'1'}, CAHPS_Box_Type = {'*'}>}RESPONSE)

)*100

)

[neelamsaroha157]

Hey Jason It didn't change anything..

[Jason_Michaelides]

Can you post an example?

[neelamsaroha157]

Hey Jason,

Here is the screenshot.

I just added the RowNo() and IsNull(RowNo()) as columns in the 'U. S. Presidents' application.

[Jason_Michaelides]

Please post that app with your changes to save me digging it out and replicating your changes!