Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
)
Hi,
Maybe try Len(Trim(RowNo())) = 0.
I didn't think that RowNo() could be null, at least I haven't seen it yet.
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
Hi Sinan,
Thanks for your reply.
For the grand total the rowno() is always null.
Thanks
Thanks Jason,
I'll try it tomorrow and update you.
Appreciate your help.
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
)
Hey Jason It didn't change anything..
Can you post an example?
Hey Jason,
Here is the screenshot.
I just added the RowNo() and IsNull(RowNo()) as columns in the 'U. S. Presidents' application.
Please post that app with your changes to save me digging it out and replicating your changes!