topic Re: Counting different occurrences in the same month in QlikView

Counting different occurrences in the same month

ttmaroney — Mon, 18 Jul 2016 15:12:34 GMT

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?

Re: Counting different occurrences in the same month

Anonymous — Mon, 18 Jul 2016 15:27:18 GMT

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.

Re: Counting different occurrences in the same month

Anonymous — Mon, 18 Jul 2016 15:36:43 GMT

use set analysis in the expression

I am assuming the following

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

(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)

Re: Counting different occurrences in the same month

ttmaroney — Mon, 18 Jul 2016 16:20:42 GMT

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.

Re: Counting different occurrences in the same month

ttmaroney — Mon, 18 Jul 2016 18:40:12 GMT

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.

Re: Counting different occurrences in the same month

Anonymous — Tue, 19 Jul 2016 07:42:29 GMT

will you provide me some sample data ...

I think something wrong with date format .

Re: Counting different occurrences in the same month

ttmaroney — Tue, 19 Jul 2016 11:55:54 GMT

See attached.Thanks for the help.

Re: Counting different occurrences in the same month

Anonymous — Wed, 20 Jul 2016 03:22:23 GMT

try this

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

=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 ...

Re: Counting different occurrences in the same month

jonathandienst — Wed, 20 Jul 2016 05:04:03 GMT

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