topic set analysis and if statements in QlikView

set analysis and if statements

Thu, 14 Apr 2011 13:28:54 GMT

Hi!

I'm trying to do this;

if([Financing cost start date]<vStartDate,

sum({$<bk_account={1401,2990}, trans_date={[Financing cost start date]}>} trans_amt_sek/100)

* ((0.06/360)*(if([Financing cost end date]<vStartDate,[Financing cost end date], vStartDate) - [Financing cost start date])), 'N/A')

But. My guess is that the set analysis doesn't like "[Financing cost start date]" which is pre-defined in another expression in the same table. It consist of a lot of if statments and i tried to replace the defined [Financing cost start date] with the huge if-statement as well but i doesn't seem to work. How do i do this? Can i use an if statment in a set anaysis and how?

//A.

set analysis and if statements

fosuzuki — Thu, 14 Apr 2011 17:34:39 GMT

Hi Anykay,

You're right, the problem is that you can't directly use if-statements in set analysis. But you can use dollar-sign expansion to evaluate a function.

For example: sum( {<trans_date={'$(=if(Condition,01/01/2011,02/02/2011))'}>} trans_amt_sek )

The syntax is to use $(= function() ), and QV will replace the function with the result of it.

Hope this helps.

Regards,

Fernando

Re: set analysis and if statements

Tue, 04 Oct 2011 18:16:18 GMT

I did something similar with "count", by using the "if" after the set and before the field that you want to sum or count:

= count ({$<[Policy Submit Date] = {'>=$(=AddYears(Min([Appt Date]), -1)) < $(=Min([Appt Date]))'},
         [Product Mktg Attendee] = , [Activity Completed] = , [Activity Cancelled] = , [Year] = , [Month] = , [Date] = >}
         distinct if([Papers Only] = 'Y' and DateType = 'Policy Submit Date', [Policy #]))

Re: set analysis and if statements

jonathandienst — Tue, 04 Oct 2011 20:02:21 GMT

Annika

Your guess is correct - you cannot use a value from another column of the table inside a set expression. Remember that set expressions are evaluated before the table is calculated, and [Financing cost start date] does not exist at that time. Apart from that I don't see any problems with your expression.

If your data set is not too large, you can convert the set expression to if statements. I think this should do it

if([Financing cost start date] < vStartDate,
Sum(If(Match(bk_account, 1401,2990) AND trans_date=[Financing cost start date], trans_amt_sek/100)
* ((0.06/360)*(if([Financing cost end date]<vStartDate,[Financing cost end date], vStartDate) - [Financing cost start date]))), 'N/A')

Set analysis is much better perfoming than sum-if expressions, so this may be slow if you have a large data set and/or complex model.

Hope this helps

Jonathan