topic Re: Row total Custamization in QlikView

Row total Custamization

neelamsaroha157 — Tue, 06 Sep 2016 19:56:41 GMT

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

Re: Row total Custamization

sinanozdemir — Tue, 06 Sep 2016 21:03:39 GMT

Hi,

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

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

Re: Row total Custamization

Jason_Michaelides — Tue, 06 Sep 2016 21:29:36 GMT

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

Re: Row total Custamization

neelamsaroha157 — Tue, 06 Sep 2016 21:57:28 GMT

‌Hi Sinan,

Thanks for your reply.

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

Thanks

Re: Row total Custamization

neelamsaroha157 — Tue, 06 Sep 2016 21:58:26 GMT

‌Thanks Jason,

I'll try it tomorrow and update you.

Appreciate your help.

Re: Row total Custamization

sunny_talwar — Tue, 06 Sep 2016 22:31:38 GMT

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

)

Re: Row total Custamization

neelamsaroha157 — Wed, 07 Sep 2016 12:33:48 GMT

Hey Jason It didn't change anything..

Re: Row total Custamization

Jason_Michaelides — Wed, 07 Sep 2016 16:14:38 GMT

Can you post an example?

Re: Row total Custamization

neelamsaroha157 — Wed, 07 Sep 2016 16:20:23 GMT

Hey Jason,

Here is the screenshot.

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

Re: Row total Custamization

Jason_Michaelides — Wed, 07 Sep 2016 16:25:32 GMT

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

Re: Row total Custamization

Jason_Michaelides — Wed, 07 Sep 2016 16:26:39 GMT

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

Re: Row total Custamization

neelamsaroha157 — Wed, 07 Sep 2016 16:46:43 GMT

Hey Jason,

Due to sensitivity of data I cannot share the application.

And my bad I attached the wrong screenshot.

Re: Row total Custamization

sunny_talwar — Wed, 07 Sep 2016 16:49:25 GMT

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

)

Re: Row total Custamization

neelamsaroha157 — Wed, 07 Sep 2016 16:52:12 GMT

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

Re: Row total Custamization

sunny_talwar — Wed, 07 Sep 2016 17:03:18 GMT

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.

Re: Row total Custamization

neelamsaroha157 — Wed, 07 Sep 2016 18:01:49 GMT

Thanks Sunny

Re: Row total Custamization

sunny_talwar — Wed, 07 Sep 2016 18:07:17 GMT

Not a problem