topic Re: Select all data between the 2 alternate state dates in QlikView

Select all data between the 2 alternate state dates

Tue, 14 Feb 2017 10:31:38 GMT

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?

Re: Select all data between the 2 alternate state dates

sunny_talwar — Tue, 14 Feb 2017 10:48:47 GMT

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

Re: Select all data between the 2 alternate state dates

tresB — Tue, 14 Feb 2017 10:59:07 GMT

Try like:

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

Re: Select all data between the 2 alternate state dates

Wed, 15 Feb 2017 01:30:45 GMT

Sorry it doesn't work

Re: Select all data between the 2 alternate state dates

Wed, 15 Feb 2017 01:33:54 GMT

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.

Re: Select all data between the 2 alternate state dates

Anil_Babu_Samineni — Wed, 15 Feb 2017 01:41:10 GMT

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

Re: Select all data between the 2 alternate state dates

sunny_talwar — Wed, 15 Feb 2017 01:44:58 GMT

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)

Re: Select all data between the 2 alternate state dates

Wed, 15 Feb 2017 01:45:31 GMT

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.

Re: Select all data between the 2 alternate state dates

Wed, 15 Feb 2017 01:51:31 GMT

thanks but sorry it doesn't work

Re: Select all data between the 2 alternate state dates

sunny_talwar — Wed, 15 Feb 2017 01:52:51 GMT

Okay