Select all data between the 2 alternate state dates

[#1]

First expression - > SUM({[Date1] * $<BONDMASTER_INSTRUMENT = {'CD', 'CP'}>} M_TP_NOMINAL * EXC_RATE)

Second expression - > SUM({[Date2] * $<BONDMASTER_INSTRUMENT = {'CD', 'CP'}>} M_TP_NOMINAL * EXC_RATE)

I have 2 alternate states to select date. Alternate state Date1 and alternate state Date 2. The first expression selects the data from date 1 which is what I want.

However, the 2nd expression should display all the data Between the dates of Date1 and Date2.

So if Date 1 is 1st Jan 2017 and Date 2 is 1st Dec 2017 and if there are 1st jan 2017, 1st feb 2017 .... 1st dec 2017, 1st jan 2018, 1st feb 2018 etc in the date field, then the second expression should display the data of 1st jan 2017 to 1st dec 2017 and exclude 2018 data.

What expression can i write in second expresion for this to happen?

[sunny_talwar]

Would you be able to share a sample to test this out?

[tresesco]

Try like:

SUM({[Date1] * [Date2] * $<BONDMASTER_INSTRUMENT = {'CD', 'CP'}>} M_TP_NOMINAL * EXC_RATE)

[#4]

Sorry it doesn't work

[#5]

For example if

Date 1                    amount

1st jan 2017            100

1st feb 2017             200

1st mar 2017            300

Date 2                      amount

1st jan 2017               100

1st feb 2017               200

1st mar 2017              300

if I select 1st Jan 2017 on Date 1, my first table shows 100. If I select 1st mar 2017 on Date 2, my second table show 300+200+100 = 600. It is the values from Date 1 to Date 2 inclusive.

[Anil_Babu_Samineni]

What if your value will selection on 1st March 2017 on Date 1

Date 1                    amount

1st jan 2017            100

1st feb 2017             200

1st mar 2017            400

Are you expecting to see -- 100+200+400 ??

[sunny_talwar]

May be like this:

Sum({[Date2]<Date = {"$(='>=' & Date(RangeMin(Max({[Date2]} Date), Max({[Date2]} Date)), 'DD/MM/YYYY') & '<=' & Date(RangeMax(Max({[Date2]} Date), Max({[Date2]} Date)), 'DD/MM/YYYY'))"}> * $<BONDMASTER_INSTRUMENT = {'CD', 'CP'}, Date = {"$(='>=' & Date(RangeMin(Max({[Date2]} Date), Max({[Date2]} Date)), 'DD/MM/YYYY') & '<=' & Date(RangeMax(Max({[Date2]} Date), Max({[Date2]} Date)), 'DD/MM/YYYY'))"}>} M_TP_NOMINAL * EXC_RATE)

[#8]

If I select 1st March 2017 on Date 1, then Table 1 will display only 1st March 2017 amount. If Date 2 is also 1st March 2017, then table 2 will just show 1st March 2017 because between Date 1 and Date 2, it is still 1st March 2017. Basically, Date 2 will always be the latest Date than Date 1.

[#9]

thanks but sorry it doesn't work

[sunny_talwar]

Okay