Expression/script help

[#1]

Hi guys,

I have a table with dimension orders and month end date (aggregation of all month dates). Also we have several flags representing is an order new, ended or renewed.

Date               OrderID     New     Ended     Cancelled     Renewed

31/01/2013     12345          -          -               -                    1

31/12/2013     12345          -          1               -                    -

How to calculate so that it will only count the renewed orders if the order is still active today?

Thanks

[#2]

Hi Nikola,

Please use this

=Count({<Renewed={1},Date={">$(=Today()) "}>} OrderID)

Thanks

AJ

[iktrayanov]

If by active today you mean Ended is not blanc try this

=Count({<Renewed={1},Ended=Ended-{1}>} OrderID)

By the way If i have to create flags I will populate the nulls with 0 in the load script.

[#4]

Hi,

Unforuntately doesnt seem to work.. If the orderID has has an eded flag status between the renewed and today it should be subscracted from the renew base.

This expression doesnt seem to work for this.

All the help is appreciated

[#5]

Is there a reason it should be populated with 0 instead of Null? Performance wise?

[iktrayanov]

It will be easier to work with 0 than trying to select nulls in set analysis.

[#7]

Generally, how do you know which Order is still Active or not ? is based on date or any flag ?

[Anonymous]

In the Script:

IF(IF(ISNULL(Ended),0,Ended) + Renewed) = 1 , 'Active Renew' , 'Ended Renew') as [_Active or Ended Renew]

If any order ID does not equal 1 than it has ended since Ended flag is 1 and Renewed flag is 1 so 1+1=2 which means its has been renewed and then ended.

[#9]

Hi,

if the order begin date is smaller and the end date is larger than the comparison date.