topic How can I write a If statement as Set Analysis? in QlikView

How can I write a If statement as Set Analysis?

Thu, 25 Nov 2010 10:45:26 GMT

Hi all,

I have a straight table chart which show inactive customer who do not have any trading activities in the past 6 weeks.

The dimension is customer and two expression showing the last trading date and number of trades outstanding.

On the report, I have a Year and Month list box for filtering. However, I want the straight table to show the same output regardless of what Year or Month I have chosen. I know this can be achieved by using 'Set Analysis' with keyword 1 instead of $ sign.

My problem here is I fail to convert the if statement into a 'Set Analysis' statement. The if statement is as follows.

=if(((today() - aggr(max([Customer Last Trade Date]), [Customer])) > 42), [Customer])

Aggregated by Customer to find out the max last trade date, if the last trade date is 42 days ago ( 6 weeks ago), then show the Customer name as the Dimension.

Anyone can help?

Thanks,

Shirley

How can I write a If statement as Set Analysis?

Thu, 25 Nov 2010 10:59:29 GMT

Hi Shirley ,

Can you please share what are the expressions you are using .

implementing the set analysis at the expression level makes this easier .

Regards,

Chakravarthy.

How can I write a If statement as Set Analysis?

Thu, 25 Nov 2010 11:10:26 GMT

Hello Shirley,

this can be achieved by using 'Set Analysis' with keyword 1 instead of $ sign.

keep in mind when using the 1: the 1 in a set statement means to discard ALL of the (users) selections. Not only for Year and Month. This would be like this

= sum( { $ <Year=, Month= >} Amount)

I wonder why you want to change your calc expr. It seems to work fine so why do you want to change it?

Best Regards,

Roland

How can I write a If statement as Set Analysis?

Thu, 25 Nov 2010 11:21:10 GMT

The user requirement is to show the static result, discard ALL the user selection.

The filter Year, Month and Date refers to customer trade date. If user select any filter like Year and Month, then the result will be only a subset and show the inactive customer who has traded on the selected date. It is not meaningful.

If the filter Year, Month and Date refers to customer last trade date, then it will be meaningful. However it is not.

So, that's why the user request to show a static result , ignore the selection from the Year, Month and Date.

How can I write a If statement as Set Analysis?

Thu, 25 Nov 2010 11:28:59 GMT

Hi Chakravarthy,

The first Dimension is Customer:

if(((today() - aggr(max([Customer Last Trade Date]), [Customer])) > 42), [Customer])

The first Expression is Last Trade Date:

aggr

(max([Customer Last Trade Date]), [Cusomer])

The second Expression is Outstanding Trades:

sum

([Customer Yet To Mature Trade Count])

The LOAD SCRIPT PART is as follows:

LOAD

[Customer] & '-' & [Product] as [Customer Product Key],

date

(max([Trade Date]), 'DD-MMM-YYYY') as [Customer Last Trade Date],

count

(distinct if([Value Date] >= today(), [Trade ID])) as [Customer Yet To Mature Trade Count]

FROM <QVD FILE>

Group

by [Customer], [Product];

distinct

Thanks !