How to give two conditions for same field in set analysis?

[#1]

Hi,

Below is my original set expression:

=sum({<Year={$(vPriorYear)}, TRANSACTION_DATE={'>=$(=YearStart(max(TRANSACTION_DATE),-1))<=$(=AddYears(max(TRANSACTION_DATE),-1))' }>} NTPV_AMT)

Now I want to add one more condition on TRANSACTION_DATE,

TRANSACTION_DATE={'>=$(=Date(date(max(TRANSACTION_DATE)-(vDays))))<=$(max(TRANSACTION_DATE))'}

I tried doing it with AND but it didn't work.

=sum({<Year={$(vPriorYear)}, TRANSACTION_DATE={'>=$(=YearStart(max(TRANSACTION_DATE),-1))<=$(=AddYears(max(TRANSACTION_DATE),-1))'  AND

'>=$(=Date(date(max(TRANSACTION_DATE)-(vDays))))<=$(max(TRANSACTION_DATE))'

}>} NTPV_AMT)

Any suggestions or help on how to achieve it.

--Sachin

[swuehl]

Try

=Sum(

{<

Year={$(vPriorYear)},

TRANSACTION_DATE =

     {">=$(=YearStart(Max(TRANSACTION_DATE),-1))<=$(=AddYears(Max(TRANSACTION_DATE),-1))"}

     *

      {">=$(=Date(Date(Max(TRANSACTION_DATE)-(vDays))))<=$(Max(TRANSACTION_DATE))"}

>}

NTPV_AMT)

[#3]

I'm stacking previous year amounts on top of current year. Current year, I'm showing as a bar chart and previous year as a symbols.

My max year, i.e 2016 has data only till May 8 whereas previous year has data till Dec 31. So the chart doesn't look good.

To fix this, I wrote below expression:

sum({<Year={$(vPriorYear)}, TRANSACTION_DATE={'>=$(=YearStart(max(TRANSACTION_DATE),-1))<=$(=AddYears(max(TRANSACTION_DATE),-1))' }>} NTPV_AMT)

And now the user wants me to show default data for last 30 days and there should be option to increase number of days.

So for this I'm adding below condition.

TRANSACTION_DATE={'>=$(=YearStart(max(TRANSACTION_DATE),-1))<=$(=AddYears(max(TRANSACTION_DATE),-1))' }

When I run individual expressions:

The first one gets rid of all the >May 8 values from 2015.

And the second one shows data for specified number of days only.

[trdandamudi]

Can you try swuehlresponse  and see if that works.. If that doesn't work out, can you hard code the values and see what you get.

[swuehl]

Could you give a more descriptive example of the two date ranges you want to combine and how you want to combine them?

For example:

Range1: Defined by {<...>}

returns in my example (with max TRANSACTION DATE YYYY-MM-DD) range 2015-05-21 to 2015-07-31

Range2: Defined by {<...>}

returns ......

I want the

a) intersection

b) union

of both

i.e. the date range .....

Thanks,

Stefan

edit:

Don't forget to follow

Dates in Set Analysis

[#6]

Hi Stefan,

Its not exactly a union. First this condition should be executed

TRANSACTION_DATE = {">=$(=YearStart(Max(TRANSACTION_DATE),-1))<=$(=AddYears(Max(TRANSACTION_DATE),-1))"}

and then

TRANSACTION_DATE =

{">=$(=Date(Date(Max(TRANSACTION_DATE)-(vDays))))<=$(Max(TRANSACTION_DATE))"}

>}

Does that make sense.

[#7]

Thanks Stefan but it didn't work.

[swuehl]

aditya17490 wrote:

Hi Stefan,

Its not exactly a union. First this condition should be executed

TRANSACTION_DATE = {">=$(=YearStart(Max(TRANSACTION_DATE),-1))<=$(=AddYears(Max(TRANSACTION_DATE),-1))"}

and then

TRANSACTION_DATE =

{">=$(=Date(Date(Max(TRANSACTION_DATE)-(vDays))))<=$(Max(TRANSACTION_DATE))"}

>}

Does that make sense.

This somehow sounds to me like you want to have an intersection of your two sets, but if I read the code right (BTW, there is an equal sign missing before the last Max()  ), the two date ranges are most probably disjunct.

That's why I asked for a more concrete example with some literal date ranges: range1, range2, your resulting range.

[CELAMBARASAN]

Hi Aditya,

Could you please tell what you are trying to implement?

1. Last Year YTD and Current Year Last N days?

if so you can use the below expr

Sum({<TRANSACTION_DATE ={">=$(=YearStart(Max(TRANSACTION_DATE),-1))<=$(=AddYears(Max(TRANSACTION_DATE),-1))"}+

{">=$(=Date(Date(Max(TRANSACTION_DATE)-$(vDays))))<=$(Max(TRANSACTION_DATE))"}

>} NTPV_AMT)

2. Last year YTD, in that Only Last N days?

if so

Sum({<TRANSACTION_DATE ={">=$(=YearStart(Max(TRANSACTION_DATE),-1))"}*

{">=$(=AddYears(Date(Max(TRANSACTION_DATE)-$(vDays)), -1))<=$(AddYears(Max(TRANSACTION_DATE), -1))"}

>} NTPV_AMT)

[jonathandienst]

AND in set expression is an intersection between the two sets to criteria, like this:

=sum({

<Year={$(vPriorYear)}, TRANSACTION_DATE={'>=$(=YearStart(max(TRANSACTION_DATE),-1))<=$(=AddYears(max(TRANSACTION_DATE),-1))'}>

*

<TRANSACTION_DATE={'>=$(=Date(date(max(TRANSACTION_DATE)-(vDays))))<=$(max(TRANSACTION_DATE))'}>

} NTPV_AMT)

EDIT: I missed much of the discussion, but I think the prior year criterion should be on the first date range, and not on the second. This would possibly make the date ranges intersect, depending on a suitable value for vDays.

[#11]

Hi Jonathan,

I changes my expression to

=Sum({<Year={$(vPriorYear)}, TRANSACTION_DATE={'>$(=Date(AddYears(Max(TRANSACTION_DATE), -1)-vDays))<=$(=AddYears(max(TRANSACTION_DATE), -1))'}

>}NTPV_AMT)

And it worked..

Thanks though.