Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Read about the latest Qlik Community enhancements on the Community News blog!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Select all data between the 2 alternate state dates

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?

9 Replies
sunny_talwar

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

tresesco
MVP
MVP

Try like:

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

Not applicable
Author

Sorry it doesn't work

Not applicable
Author

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 ??

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
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)

Not applicable
Author

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.

Not applicable
Author

thanks but sorry it doesn't work

sunny_talwar

Okay