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

[swuehl]

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)

[#3]

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.

[#4]

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