topic Re: How to display rows with zero values in QlikView

How to display rows with zero values

farheenayesha — Wed, 21 Mar 2018 18:25:03 GMT

Hi,

I am trying to show utilization % of sheet in pivot table. However i am not able to show the sheets with zero values in the pivot table.

I am having 3 documents(Doc1,Doc2 & Doc3) and each has got few sheets.I am facing two issues in my dashboard.

1. Doc1 has got 14 sheets and out of which only 11 has utilization % and the other three doesn't have any data. For these 3 sheets i want to show value as zero. However with my expression, these three rows are not getting displayed in pivot table. Please find the data of the pivot table below.

Expression:Count(distinct UNIX_ID_GOV)/count(DISTINCT total UNIX_ID_RST)

Sheet	GOV	RST	GOV/RST	Document
Sheet1	112	112	100.0%	Doc1
Sheet2	112	112	100.0%	Doc1
Sheet3	110	112	98.2%	Doc1
Sheet4	109	112	97.3%	Doc1
Sheet5	107	112	95.5%	Doc1
Sheet6	107	112	95.5%	Doc1
Sheet7	103	112	92.0%	Doc1
Sheet8	98	112	87.5%	Doc1
Sheet9	91	112	81.3%	Doc1
Sheet10	87	112	77.7%	Doc1
Sheet11	64	112	57.1%	Doc1

However, when i am checking 'Show all Values' at dimension level it is showing the other sheets of other docs as well (Doc2 & Doc3). Which I dont want. Please find the data below.

Sheet	GOV	RST	GOV/RST	Document
Sheet1	112	112	100.0%	Doc1
Sheet2	112	112	100.0%	Doc1
Sheet3	110	112	98.2%	Doc1
Sheet4	109	112	97.3%	Doc1
Sheet5	107	112	95.5%	Doc1
Sheet6	107	112	95.5%	Doc1
Sheet7	103	112	92.0%	Doc1
Sheet8	98	112	87.5%	Doc1
Sheet9	91	112	81.3%	Doc1
Sheet10	87	112	77.7%	Doc1
Sheet11	64	112	57.1%	Doc1
Sheet12	0	112	0.0%	Doc1
Sheet13	0	112	0.0%	Doc1
Sheet14	0	112	0.0%	Doc1
Sheet15	0	112	0.0%	Doc2
Sheet16	0	112	0.0%	Doc2
Sheet17	0	112	0.0%	Doc2
Sheet18	0	112	0.0%	Doc2
Sheet19	0	112	0.0%	Doc2
Sheet20	0	112	0.0%	Doc2
Sheet21	0	112	0.0%	Doc2
Sheet32	0	112	0.0%	Doc2
Sheet33	0	112	0.0%	Doc3
Sheet34	0	112	0.0%	Doc3
Sheet35	0	112	0.0%	Doc3
Sheet47	0	112	0.0%	Doc3
Sheet48	0	112	0.0%	Doc3
Sheet49	0	112	0.0%	Doc3
Sheet50	0	112	0.0%	Doc3
Sheet51	0	112	0.0%	Doc3
Sheet64	0	112	0.0%	Doc3
Sheet65	0	112	0.0%	Doc3

I want the Pivot as below:

Sheet1	112	112	100.0%	Doc1
Sheet2	112	112	100.0%	Doc1
Sheet3	110	112	98.2%	Doc1
Sheet4	109	112	97.3%	Doc1
Sheet5	107	112	95.5%	Doc1
Sheet6	107	112	95.5%	Doc1
Sheet7	103	112	92.0%	Doc1
Sheet8	98	112	87.5%	Doc1
Sheet9	91	112	81.3%	Doc1
Sheet10	87	112	77.7%	Doc1
Sheet11	64	112	57.1%	Doc1
Sheet12	0	112	0.0%	Doc1
Sheet13	0	112	0.0%	Doc1
Sheet14	0	112	0.0%	Doc1

2 . My second issue is showing the count of these sheets as 11. Currently with my expression(Count (distinct Sheet)) it is showing the count as 11. It is not counting the rows with zero values. I want the count of sheet as 14.

Kindly help me to resolve these 2 issues.

stalwar1‌@

Re: How to display rows with zero values

sunny_talwar — Wed, 21 Mar 2018 18:50:36 GMT

May be use a calculated dimension

If(Document = 'Doc1', Sheet)

and then keep 'Suppress Zero Values' unchecked, but check 'Suppress When Value Is Null' on the dimension's tab

Re: How to display rows with zero values

farheenayesha — Wed, 21 Mar 2018 19:04:05 GMT

Thanks for the quick reply Sunny.

I dont want to hard code the document values. Let me know if there is any dynamic way to achieve it.

And i want these two solutions in Qliksense. Any idea on how to implement this in Qliksense?

Re: How to display rows with zero values

sunny_talwar — Wed, 21 Mar 2018 19:05:38 GMT

What do you mean when you say Dynamic? Dynamic based on what?

Re: How to display rows with zero values

farheenayesha — Wed, 21 Mar 2018 19:09:31 GMT

Dynamic based on selecting Document.

Re: How to display rows with zero values

sunny_talwar — Wed, 21 Mar 2018 19:10:50 GMT

Would you be able to share a sample to show what is going on? I am not sure I understand how you have this all setup

Re: How to display rows with zero values

farheenayesha — Thu, 22 Mar 2018 10:27:25 GMT

Hi Sunny,

Pls find the sample application attached. In the qvw i have two docname fields, one is Docname and another is Tab_Docname.

When i select Tab_Docname then i get the correct count of sheet and rows with the zero values in the pivot table.

However, when i make selection in the Docname i get count as 11 and rows with zero values will not show up.

I want to have Docname as filter in my dashboard and to show the count as 14 along with zero value rows. Sample is in Qlikview but i want the solution in Qliksense. Please find the sample attached

Re: How to display rows with zero values

qlikview979 — Thu, 22 Mar 2018 10:39:43 GMT

theresaa‌

Re: How to display rows with zero values

sunny_talwar — Thu, 22 Mar 2018 10:50:42 GMT

Try with this expression

=Count(distinct Users) + Sum({1<Tab_DocName = p(Tab_DocName)>}0)

Re: How to display rows with zero values

farheenayesha — Thu, 22 Mar 2018 11:08:26 GMT

You are genius Sunny

It would be great if you could help me to achieve the count as well. Currently i am getting 11. but i need the count as 14.

Re: How to display rows with zero values

sunny_talwar — Thu, 22 Mar 2018 11:09:50 GMT

This

=Count(DISTINCT {<Tab_DocName = p(Tab_DocName), DocName>} Sheet)

Re: How to display rows with zero values

farheenayesha — Thu, 22 Mar 2018 12:15:51 GMT

U solved my biggest problem again. Thank you soooooooooo much