How to count the number of projects per month and make a column chart?

[RayWu]

How to count the number of projects per month and make a column chart? Thank you.

 

[Taoufiq_Zarra]

@RayWu  Maye be :

In load Script :

Data:


LOAD Project,Date#(Begin,'YYYY/M/D') as Begin,Date#(Due,'YYYY/M/D') as Due INLINE [
Project,Begin,Due
Projet A,2020/10/19,2020/11/20
Projet B,2020/11/2,2020/12/31
Projet C,2020/11/2,2020/12/30
Projet D,2020/12/1,2021/1/31
Projet E,2021/1/1,2021/2/28
];
left join load distinct  Project,Date(MonthStart(Begin + IterNo() - 1),'MMM-YY')  as Month resident Data

While MonthStart(Begin) + IterNo() - 1 <= MonthStart(Due);

then in chart

Dimension : Month

Measure : =count(Project)

output:

[RayWu]

Thank you for your help.

"Project, begin, due" has these three fields in the database. How should the script be written?

[RayWu]

I'm not good at writing scripts. This problem is very difficult for me. Please help again, thank you!

[Taoufiq_Zarra]

@RayWu  In the Script

Data:

LOAD Project,Date#(Begin,'YYYY/M/D') as Begin,Date#(Due,'YYYY/M/D') as Due
;
SQL SELECT *

FROM YOURDATABASE;

left join load distinct Project,Date(MonthStart(Begin + IterNo() - 1),'MMM-YY') as Month resident Data

While MonthStart(Begin) + IterNo() - 1 <= MonthStart(Due);

you can just change DATABASE by your database

Project, begin, due are the fields from your database

[Vegar]

Replace the INLINE [...]  part in @Taoufiq_Zarra  script sample with a reference to your source, something like this: FROM  dbo.YourDbTable

[RayWu]

I tried the script you provided, but there were some problems. Would you please take a look at it for me?

 

 

 

[Vegar]

It could be that your [begin] and [due] are not recognized as dates, hence your format is not 'YYYY/M/D' it is 'YYYY/MM/DD hh:mm:ss'. 

Try replacing editing their load to the following.

Dayname(Date#(Begin,'YYYY/MM/DD hh:mm:ss')) as Begin,
Dayname(Date#(Due,,'YYYY/MM/DD hh:mm:ss')) as Due

[RayWu]

Sorry for replying to your message so late. I have tested the method you provided, but there are still some problems on my side. Due to the urgency of the project, we have dealt with this problem in the database. Thank you very much.

@Taoufiq_Zarra  Thank you very much.

[ArnadoSandoval]

@Vegar 

If they are using MS Sql, Begin is a reserved word, it is on MS T-Sql documentation, perhaps, that was part of their problem.