Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
Would you be able to share a sample to test this out?
Try like:
SUM({[Date1] * [Date2] * $<BONDMASTER_INSTRUMENT = {'CD', 'CP'}>} M_TP_NOMINAL * EXC_RATE)
Sorry it doesn't work
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.
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 ??
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)
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.
thanks but sorry it doesn't work
Okay