topic Re: Help on Date Range for Set Analysis in QlikView

Help on Date Range for Set Analysis

Anonymous — Tue, 24 Apr 2018 18:58:03 GMT

Hello Qlikview Community

I am trying to calculate the count of orders for a specific order type (ZPM3) within a specific date range.

=Count({<Order.Type={'ZPM3'},Calendar.Date={">=2015-01-01<=RCAOrderDate"}>}[Order.Number of orders])

Re: Help on Date Range for Set Analysis

sunny_talwar — Tue, 24 Apr 2018 19:51:42 GMT

What is RCAOrderDate here? a single value selected or single value possible?

Re: Help on Date Range for Set Analysis

Qrishna — Tue, 24 Apr 2018 20:11:06 GMT

=Count({<Order.Type={'ZPM3'},Calendar.Date={">= $(=date(MakeDate(2015,01,01),'YYYY-MM-DD') ) <=RCAOrderDate"}>}[Order.Number of orders])

make sure RCAOrderDate is a value (not a field) and in the same format as Calendar.Date

Re: Help on Date Range for Set Analysis

Anonymous — Wed, 25 Apr 2018 11:26:08 GMT

Good Morning Krishna,

I believe that is where I am having a problem.

I have RCAOrderDate as a field in my expressions, it is in the same format as Calendar.Date.

Re: Help on Date Range for Set Analysis

sunny_talwar — Wed, 25 Apr 2018 12:42:41 GMT

In that case, I don't think you will be able to use set analysis here... I suggest using Aggr with an if statement to get this resolved... or handle this in the script to create a flag and then use the flag in the set analysis....

Re: Help on Date Range for Set Analysis

Qrishna — Wed, 25 Apr 2018 13:55:13 GMT

try this in the expression:

= if( Calendar.Date <=RCAOrderDate ,

Count({<Order.Type={'ZPM3'},Calendar.Date={">= $(=date(MakeDate(2015,01,01),'YYYY-MM-DD') ) "}>} [Order.Number of orders]), 'Put Your ELSE statment'

)

if RCAOrderDate is calculated measure , then do this -

= if( Calendar.Date <=Column(#) ,

Count({<Order.Type={'ZPM3'},Calendar.Date={">= $(=date(MakeDate(2015,01,01),'YYYY-MM-DD') ) "}>} [Order.Number of orders]), 'Put Your ELSE statment'

)

where # is the column number of calculated measure RCAOrderDate

Re: Help on Date Range for Set Analysis

sasiparupudi1 — Fri, 27 Apr 2018 20:14:33 GMT

please share the expression RCAOrderDate

Re: Help on Date Range for Set Analysis

Anonymous — Mon, 30 Apr 2018 12:06:30 GMT

=only({$<[Order.Type]={'ZPM3'},[Order.Maint Act Type]={'RCA'}>}[Order.Start Date])

Re: Help on Date Range for Set Analysis

sasiparupudi1 — Mon, 30 Apr 2018 17:48:14 GMT

May be try

Count({<Order.Type={'ZPM3'},Calendar.Date={">=2015-01-01<=$(=only({$<[Order.Type]={'ZPM3'},[Order.Maint Act Type]={'RCA'}>}[Order.Start Date]))"}>}[Order.Number of orders])

if it does not work , try breaking the expressions and see if you get theresults

Count({<Order.Type={'ZPM3'},Calendar.Date={">=2015-01-01"}>}[Order.Number of orders])

Count({<Order.Type={'ZPM3'},Calendar.Date={"<=$(=only({$<[Order.Type]={'ZPM3'},[Order.Maint Act Type]={'RCA'}>}[Order.Start Date]))"}>}[Order.Number of orders])

if they dont work, pl share a sample