Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Would you be able to share a sample to look at this?
Hi,
please activate "Show All Values" in the dimensions tab.
Regards
Sabine
Try
=count(doc_key) * Avg({1} 1)
edit:
Or maybe
=Rangesum( count(doc_key), Sum({1} 0) )
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
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
Replace the set analysis with
{1<YearField = p(YearField), MonthField=p(MonthField)>}
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.
Sunny, Thank you for your answer. It is complicated to share it as it is containing confidential data.
Regards.
Let me complete :
Now the order of my dimension is messed up... when I select a departement.
Otherwise it is fine.