Chart not showing zero values

[#1]

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";

4)

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

[sunny_talwar]

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

[#3]

Hi,

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

Regards

Sabine

[swuehl]

Try

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

edit:

Or maybe

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

[#5]

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

[#6]

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

[swuehl]

Replace the set analysis with

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

[#8]

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.

[#9]

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

Regards.

[#10]

Let me complete :

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

Otherwise it is fine.