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
And that screenshot doesn't look like your previous one...
Hey Jason,
Due to sensitivity of data I cannot share the application.
And my bad I attached the wrong screenshot.
Did you get a chance to try what I proposed below?
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
)
Yeah Sunny, It did.
I knew this would work for sure and it did. But it just makes the calculation lengthy and more resource intensive.
Is there any other way to do it??
Thanks
Hahahaha, I wish you had mention that before because we didn't know that you were trying to optimize your expression here. I think all ways would require you to use the the count expression to check if it is greater than 0 or not. That is just because how your expression is setup.
Thanks Sunny
Not a problem