topic Re: Expression/script help in QlikView

Expression/script help

Fri, 17 Jan 2014 21:04:03 GMT

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

Re: Expression/script help

Fri, 17 Jan 2014 21:20:01 GMT

Hi Nikola,

Please use this

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

Thanks

Re: Expression/script help

iktrayanov — Fri, 17 Jan 2014 21:38:01 GMT

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.

Re: Expression/script help

Fri, 17 Jan 2014 21:42:24 GMT

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

Re: Re: Expression/script help

Fri, 17 Jan 2014 21:46:14 GMT

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

Re: Re: Expression/script help

iktrayanov — Fri, 17 Jan 2014 21:52:03 GMT

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

Re: Expression/script help

Sat, 18 Jan 2014 02:27:17 GMT

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

Re: Expression/script help

Anonymous — Sat, 18 Jan 2014 05:06:09 GMT

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.

Re: Re: Expression/script help

Sat, 18 Jan 2014 09:19:15 GMT

Hi,

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