How to set expression for field comparsion?

[Anonymous]

Hi All,

From	To	Date
1/18/2016	12/12/2016	3/3/2016
2/14/2016	1/17/2017	1/1/2015
3/14/2016	4/1/2016	2/6//2016
4/19/2016	7/7/2017	7/7/2016

I would like to count the number of record where date > from  and date < to in the pivot table with dimension month(date)&year(date)

Many Thx!

Best Regards,

Louis

[girirajsinh]

try something

count( {<date={">=from_date<To_date"}>} fielda)

it would help to develop exact solution if you can share some sample data.

bR

Giri

[MK_QSL]

SUM(If(Date > From and Date < To,1,0))

or

SUM(Aggr(SUM(If(Date > From and Date < To,1,0)),Date,From,To))

[vinieme12]

TRY

=count( {<Date={"=Date > From and Date < To"}>} Date)

[sunny_talwar · Accepted solution]

This may not work if we have a single date associated with multiple From and To. I think creating this in the script might be a better option

LOAD If(Date > from_date and Date < To_date, 1, 0) as Flag,

           ....

and then use like this

Count({<Flag = {1}>} Measure)

[vinieme12]

This work's sunny

Tested it

[sunny_talwar]

Even when date is associated with multiple From and To?

[vinieme12]

set_analysis_intra-record.qvw

[sunny_talwar]

Look at the attached example. Here I added three new rows and the column Record will tell you which rows are new.

[vinieme12]

Ok, then the only change you need to do is to verify with the Rowkey for the record which should be RowNum

you can try

=Count( {<RowNum={"=Date > From and Date < To"}>} Date)

Cheers

V