Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Set analysis count

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

1 Solution

Accepted Solutions
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi,

     Find the attachment for clarification.

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!

View solution in original post

17 Replies
alexandros17
Partner - Champion III
Partner - Champion III

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
Creator II
Creator II

Try as below.

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

Not applicable
Author

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
Creator II
Creator II

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

Not applicable
Author

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
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
Not applicable
Author

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
Partner Ambassador/MVP
Partner Ambassador/MVP

HI,

     Try this.

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

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
Not applicable
Author

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.