14 Replies Latest reply: Jun 10, 2014 4:39 AM by Jacob Baruch

Set analysis and two dates Fields

Hi,

I've got in my module,

the following tables:

Item Table:

Item,

Date

Sales:

Item,

SaleDate,

Qty

I need to calculate for each item sum(Qty) where SaleDate<= Date.

I know set analysis back and forward but somehow i can't manage to do that.

Can someone know how to solve this issue?

Thnx a lot

• Re: Set analysis and two dates Fields

sum({<SaleDate = {"<= Date"}>} Qty)

let me know if this works for you.

• Re: Set analysis and two dates Fields

sum({<SaleDate ={ "<= Date"}>} Qty)

• Re: Set analysis and two dates Fields

See the set analysis used in the tables in the attached file.

• Re: Set analysis and two dates Fields

Thanks for the help,

but I've got two date fields, so i need to compare them without variables

• Re: Set analysis and two dates Fields

I understand.

So your expression would be: sum({<SalesDate = {"<=Date"}>}Qty)

• Re: Set analysis and two dates Fields

tried it and similar ups and down without a success

• Re: Set analysis and two dates Fields

Hi,

did you try using an if ?

sum(if( SaleDate<=Date,Qty))

• Re: Set analysis and two dates Fields

Great & Simple

• Re: Set analysis and two dates Fields

While the solution works, if statements produce slower results than set analysis.

• Re: Set analysis and two dates Fields

I dont know if im missing somethign here, but you dont need set analysis. You just need an if statement in the expr =sum(if(SalesDate<=Date,Qty)). Please check the attached.

• Re: Set analysis and two dates Fields

Yes both solutions will work, but if statements produce slower results than using set analysis. It is optimal to use set analysis over if statements whenever possible

• Re: Set analysis and two dates Fields

I know set analysis is quicker,

still didn't get right syntax for set analysis that works for that.

• Re: Set analysis and two dates Fields

You cannot compare two different fields in set analysis. You have to use if.

• Re: Set analysis and two dates Fields

Does anyone knows how to deal the issue in the script itself?

mainly because user perfomance