Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
neelamsaroha157
Specialist II
Specialist II

Row total Custamization

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:

Capture2.PNG

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.

Capture3.PNG

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

Would appreciate any help.

Thanks

16 Replies
Jason_Michaelides
Luminary Alumni
Luminary Alumni

And that screenshot doesn't look like your previous one...

neelamsaroha157
Specialist II
Specialist II
Author

Hey Jason,

Due to sensitivity of data I cannot share the application.

And my bad I attached the wrong screenshot.

Capture5.PNG

sunny_talwar

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

)

neelamsaroha157
Specialist II
Specialist II
Author

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

sunny_talwar

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.

neelamsaroha157
Specialist II
Specialist II
Author

Thanks Sunny

sunny_talwar

Not a problem