topic Re: set analysis or if statement in QlikView

set analysis or if statement

userid128223 — Fri, 02 Nov 2012 15:53:22 GMT

I have fields like below:

FieldName Format comments

Service Date: MM/DD/YYYY

Time Purchase date: MM/DD/YYYY

Time Purchase Hours: Number

Billable time: T or F // T = true F=false

What I want to calculate:

If Time Purchase Date from TODAYS DATE is less then 14 month AND Billable Time = T (true) then

total timepurchsehours for that period of time.

(exclude any time purchse if it is more then 14 months old from todays date or billable time is set to "N")

How can i write expression to calculate above.

Re: set analysis or if statement

Fri, 02 Nov 2012 16:07:13 GMT

Try this:

if (PurchaseDate>today()-addMonths(PurchaseDate,-14) AND BillableTime='T',sum(timepurchsehours ))

TMF

Re: set analysis or if statement

Fri, 02 Nov 2012 16:07:25 GMT

Sum({$<[Billable Time] = {'T'}, [Time Purchase Date] = {'>=ADDMONTHS(TODAY(),-14)'}>}[Time Purchase Hours])

I havent tested but it shouldnt be far out.

Re: set analysis or if statement

userid128223 — Fri, 02 Nov 2012 17:10:15 GMT

Thanks for the answers. Since I need to calculate more field based on results of one. I would like to avoid IF statement. hence I would like to do this via set analysis.

Felim Below works for me.

=Sum({$<BILLABLE = {'F'}>}TimePurchasedHours)

however the other part where it check for 14 months do not work.

When i use below If statement I am getting correct answer.

= if( TimePurchaseDate > AddMonths(today(),-14) AND BILLABLE='T', sum(TimePurchasedHours),0)

When I use below setanalysis i get wrong results.

=Sum({$<BILLABLE = {'T'}, TimePurchaseDate = {'>=ADDMONTHS(TODAY(),-14)'}>}TimePurchasedHours)

I am using date filed to check if results are correct or not.

Re: set analysis or if statement

MayilVahanan — Fri, 02 Nov 2012 17:17:44 GMT

Try like this

=Sum({$<BILLABLE = {'T'}, TimePurchaseDate = {'>=$(=ADDMONTHS(TODAY(),-14))'}>}TimePurchasedHours)

Re: set analysis or if statement

userid128223 — Fri, 02 Nov 2012 18:52:40 GMT

Great Thanks Ramasamy It worked.