set analysis or if statement

[userid128223]

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.

[#2]

Hi

Try this:

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

TMF

[#3]

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

I havent tested but it shouldnt be far out.

[userid128223]

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.

[MayilVahanan · Accepted solution]

HI

Try like this

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

[userid128223]

Great Thanks Ramasamy It worked.