Set analysis count

[#1]

Hi

I have two date fields DATE1 and DATE2 and I am intending to count ID. How can I write below expression in set analysis.

COUNT(IF(Date1=Date2, ID))

[alexandros17]

Set analysis doesn't apply to test on fields but on fixed values

So you can load in your script a new field TEST

Load

     ....

if(Data1=Data2,1,0) as Test

from ...

then you expression will be

Count({$ <Test={1}>} ID)

[srinivasa1]

Try as below.

count({$<Date1= Date2>} ID)

[#4]

Calendar is generated by DATE1. For some reason DATE1 = DATE2 doesnt seem to work. Format is dd/mm/yyyy hh:mm:ss. Checked the format too. Acting like a beast.

[srinivasa1]

What value coming in date1 and date2? check by adding both in list box and compare. also check trim and null value

[#6]

Did that too. No null values. When I select dates from list box the cchart gives right numbers but not when in an expression.

[kaushiknsolanki]

Hi,

     Try this.

Load

     ....

if(Date(date#(Data1,'DD/MM/YYYY hh:mm:ss'))=Date(date#(Data2,'DD/MM/YYYY hh:mm:ss')),1,0) as Test

from ...

then you expression will be

Count({$ <Test={1}>} ID)

Regards,

Kaushik Solanki

[#8]

TABLE1: Calendar controlled by DATE1

ID                           DATE1                          

d123                01/01/2013 00:00:00             

d456                01/01/2013 00:00:00

d789                15/05/2013 00:00:00

d578                21/08/2013 00:00:00            

d798                07/07/2013 00:00:00

d487                15/09/2013 00:00:00

TABLE2:

      DATE2                                     DATE3

01/01/2013 00:00:00               05/08/2013 00:00:00

01/01/2013 00:00:00               01/01/2013 00:00:00

10/06/2013 00:00:00               18/07/2013 00:00:00

07/07/2013 00:00:00               15/05/2013 00:00:00

07/07/2013 00:00:00               21/08/2013 00:00:00

20/09/2013 00:00:00               15/09/2013 00:00:00

I am trying to match DATE1=DATE2 and count the ID. Because the calendar is controlled by DATE1 the count doesnt change. But when I select date from DATE2 the count is correct.

I hope I put the scenario correctly.

[kaushiknsolanki]

HI,

     Try this.

          Count({<Date1 = p(Date2)>}ID)

Regards,

Kaushik Solanki

[#10]

I have a chart with DATE1 as dimension as it controls the calendar. The count works but only when I select dates from DATE2 not DATE1. How can DATE2 be controlled with DATE1. Cant join them. The app is huge and it affects the other tabs.