topic Chart not showing zero values in QlikView

Chart not showing zero values

Thu, 26 Jan 2017 11:50:46 GMT

Hello,

I'll explain my current situation in 4 points :

1) I wrote a calendar :

LET vMinDate = Num(Makedate(2015,1,1));

LET vMaxDate = Num(Makedate(Year(Now()),Month(Now()),Day(Now())));

ChampDate:

LOAD date($(vMinDate)+IterNo()-1) as MaDate

AUTOGENERATE (1)

WHILE $(vMinDate)+IterNo()-1<= $(vMaxDate);

Calendrier:

LOAD

MaDate as Date,

Year(MaDate) as Annee,

'T ' & Num(Ceil(Month(MaDate)/3),'(ROM)0') as Trimestre,

Month(MaDate) as Mois,

dual(applymap('MonthMap', Month(MaDate)), Month(MaDate)) as MoisNom,

MonthEnd(MaDate)as DateFinMois,

Week(MaDate) as Semaine,

Year(MaDate) & ' ' & Num(Week(MaDate), '00') as AnneeSemaine,

Weekday(MaDate) as JourSemaine,

Day(MaDate) as Jour

2) I have a table with documents :

documents:

Load *, TextBetween("doc_statement", '<METAITEM NAME="UnofficialTitle"><VALUE>', '</VALUE></METAITEM>') as documenttitle,

TextBetween("doc_statement", '<METAITEM NAME="ToProject"><VALUE>', '</VALUE></METAITEM>') as projectid,

Year(doc_insertdate) & ' ' & Num(Week(doc_insertdate), '00') as AnneeSemaine;

SQL SELECT "doc_docno",

"doc_key",

"doc_insertdate",

Year("doc_insertdate") as Annee,

Month("doc_insertdate") as Mois,

(...)

FROM SI360PRD.dbo."doc_document";

3 ) I have a table with projects (one document has a foreign key "projectid" pointing to his project)

Projets:

SQL SELECT "pr_recno" as projectid,

"pr_prc_recno",

"pr_prd_recno",

"pr_ct_owner" as contactId,

"pr_name",

"pr_desc",

...

FROM SI360PRD.dbo."pr_project";

I have a chart, with

Dimension : AnneeSemaine

Expression : =count(doc_key)

I want my chart to show a line even for the dimensions where expression value is 0.

So I went to chart properties > presentation > uncheck 'suppress zero-values' and it is working fine.

But when I select a departement (pr_project.pr_desc) , the chart updates itself, and the zero values are ignored, just as if the 'suppress zero-values' was checked !

Can you help me please ?

Thank you in advance,

Jean-Yves

Re: Chart not showing zero values

sunny_talwar — Thu, 26 Jan 2017 11:56:47 GMT

Would you be able to share a sample to look at this?

Re: Chart not showing zero values

Thu, 26 Jan 2017 13:39:11 GMT

Hi,

please activate "Show All Values" in the dimensions tab.

Regards

Sabine

Re: Chart not showing zero values

swuehl — Thu, 26 Jan 2017 14:07:13 GMT

Try

=count(doc_key) * Avg({1} 1)

edit:

Or maybe

=Rangesum( count(doc_key), Sum({1} 0) )

Re: Chart not showing zero values

Fri, 27 Jan 2017 14:39:00 GMT

Hi Sabine,

Activating Show all values is fine, but then my chart becomes unreactive to my year list and my month list, so I am looking for an alternative solution.

thank you anyway, regards,

J-Y

Re: Chart not showing zero values

Fri, 27 Jan 2017 14:52:46 GMT

Hi Stefan,

Modifying the Expression is fine, but then my chart becomes unreactive to my year list and my month list, so I am looking for an alternative solution. I would like to see the zero values in my chart AND my chart to be reactive to a year / month selection (as it is now, but now it does not show the zero values).

thank you anyway, regards,

J-Y

Re: Chart not showing zero values

swuehl — Fri, 27 Jan 2017 14:56:27 GMT

Replace the set analysis with

{1<YearField = p(YearField), MonthField=p(MonthField)>}

Re: Chart not showing zero values

Fri, 27 Jan 2017 15:06:06 GMT

Thank you again for your answer.

=Rangesum( count(doc_key), Sum({1<Annee = p(Annee), Mois=p(Mois)>} 0) )

seems to work.

but now the order of my dimension is messed up.

in the sort tab i set : Expression > ascending > YearWeek (wich is a concatenation of the year field and the week field) , since I modified my expression with your solution, my numbers are mixed.

Can you still help please ? Thank you.

Re: Chart not showing zero values

Fri, 27 Jan 2017 15:08:22 GMT

Sunny, Thank you for your answer. It is complicated to share it as it is containing confidential data.

Regards.

Re: Chart not showing zero values

Fri, 27 Jan 2017 15:12:44 GMT

Let me complete :

Now the order of my dimension is messed up... when I select a departement.

Otherwise it is fine.

Re: Chart not showing zero values

swuehl — Fri, 27 Jan 2017 15:14:18 GMT

Is your dimension YearWeek? Wouldn't a sort by number or text just work then?

I am not really sure what causes this, but if you need to use a sort expression, try adding the set analysis to the expression as well:

=Max({1<Annee = p(Annee), Mois=p(Mois)>} YearWeek)

Re: Chart not showing zero values

Fri, 27 Jan 2017 15:14:45 GMT

sorry again let me complete :

Now the order of my dimension is messed up... when I select a departement.

and the zero values disapear when I select a departement too.

Otherwise it is fine.

Re: Chart not showing zero values

Fri, 27 Jan 2017 15:19:18 GMT

Ok !

So :

Expression : =Rangesum( count(doc_key), Sum({1<Annee = p(Annee), Mois=p(Mois)>} 0) )

and sort expression : =Max({1<Annee = p(Annee), Mois=p(Mois)>} YearWeek)

(and Presentation : Suppress zero values)

is the solution for me.

Thank you a lot, your help is very appreciated, Stefan.

Regards,

Jean-Yves