Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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))
Hi,
Find the attachment for clarification.
Regards,
Kaushik Solanki
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)
Try as below.
count({$<Date1= Date2>} ID)
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.
What value coming in date1 and date2? check by adding both in list box and compare. also check trim and null value
Did that too. No null values. When I select dates from list box the cchart gives right numbers but not when in an expression.
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
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.
HI,
Try this.
Count({<Date1 = p(Date2)>}ID)
Regards,
Kaushik Solanki
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.