Counting different occurrences in the same month

[ttmaroney]

I have an application with a list of projects and their characteristics (dates created, started, and finished (MM-YYYY) as well as Phase (completed, closed, …) and others. I have a dimension MonthYear (also MM-YYYY). What I am shooting for is a straight table with MonthYear as the first dimension and the following calculated dimensions:

• Projects Converted (Count projects where CreatedMonthYear =  MonthYear)
• Projects Started (Count projects where StartMonthYear =  MonthYear)
• Projects Cancelled (Count projects where FinishMonthYear =  MonthYear and Status = ‘Cancelled’)
• Projects Completed (Count projects where FinishMonthYear =  MonthYear and Status = ‘Completed')

Looking for something like this:

MonthYear         Projects Converted         Projects Started     Projects Completed      Projects Cancelled 

Jul-2016                               4                               3                              6                                  1

Jun-2016                              5                               4                              7                                  2

…

  How do I write the expression for the bullet points above?

[Anonymous]

Hi

Maybe you could use the canonical dates approach as described in Henric's excellent post Canonical Date. Then you could just use an expression such as count({Phase={'Completed'}>}Projects). And the same for each phase you need to show.

Hope this is useful for you.

[Anonymous]

Hi

use set analysis in the expression

I am assuming the following

(dates created, started, and finished (MM-YYYY)

as

(dates created,  dates  started, and dates  finished (MM-YYYY)

• Projects Converted (Count projects where CreatedMonthYear =  MonthYear)

count({<MonthYear={ '$(=Date([dates created],'MM-YYYY' )'}  >} projects)

• Projects Started (Count projects where StartMonthYear =  MonthYear)

count({<MonthYear={ '$(=Date([dates started],'MM-YYYY' )'}  >} projects)

• Projects Cancelled (Count projects where FinishMonthYear =  MonthYear and Status = ‘Cancelled’)

count({<MonthYear={ '$(=Date([dates finished],'MM-YYYY' )'} , Status={'Cancelled’} >} projects)

[ttmaroney]

Alluraiah,

Thanks for the reply. When I try these, starting with the first, I am getting "error in calculated dimension'. I included screen shots below. What am I doing wrong?  Also, if they all have the same format, do I need to specify the date format?

It says it's ok, yet it underlines some in red, indicating something is wrong.

[ttmaroney]

I have tried all of these with no luck (just for the first calculation based on creation date:

=count({<CreatedMonthYear={[date([MonthYear],'MMM-YYYY'}>}distinct [Investment ID])

=count({<MonthYear={[date([CreatedMonthYear],'MMM-YYYY'}>}distinct [Investment ID]) 

=count({<MonthYear={CreatedMonthYear}>}distinct [Investment ID]) 

=count({<(Date(MonthYear,'MMM-YYYY'))= (Date(CreatedMonthYear, 'MMM-YYYY')}>} distinct [Investment ID]) )

=count({[<[CreatedMonthYear]={$(Date(MonthYear,'MMM-YYYY')"}>}distinct [Investment ID])

I'm doing something fundamentally wrong, I just don't know what it is.

[Anonymous]

Hi

will you provide me some sample data ...

I think something wrong with date format .

[ttmaroney]

See attached.Thanks for the help.

[Anonymous]

Hi

try this

=count({$<[MonthYear]={"$(=Date(CreatedMonthYear,'MMM-YYYY'))"}>} DISTINCT [Investment ID])

or

=count({$<[MonthYear]={">=$(=Date(max(CreatedMonthYear),'MMM-YYYY'))<=$(=Date(min(CreatedMonthYear),'MMM-YYYY'))"}>} DISTINCT [Investment ID])

...

but I am realizing now , Canonical Date  should be right ...

[jonathandienst]

You cannot use a set expression to select based on a dimension value. The set expression is evaluated once for the table, and not on a row-by-row basis. You can use an island dimension and sum(if()) expressions, or the approach in the link suggested above, Canonical Date