Help on Date Range for Set Analysis

[Anonymous]

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

[sunny_talwar]

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

[krishna_2644]

=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

[Anonymous]

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.

[sunny_talwar]

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

[krishna_2644]

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

[sasiparupudi1]

please share the expression RCAOrderDate

[Anonymous]

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

[sasiparupudi1]

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