Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Expression for 0-1:
=count(if(Status='Open' and [As On Date]-[Commissioning Date]<2 ,Status='Closed' and [Closure Date]-[Commissioning Date]<2, [WQMS ID]))
Dimension Status has 2 values 'Open' and 'Closed' .
Obtained Result :
Status | 0-1 |
Closed | 1 |
Open | 6 |
Desired Result:
Status | 0-1 |
Closed | 1 |
Open | 2 |
Data :
Commissioning Date | As On Date | WQMS ID | Closure Date | Diff | Status |
4/3/2013 | 4/5/2013 | 1001 | 2 | Open | |
4/2/2013 | 4/5/2013 | 1002 | 3 | Open | |
4/1/2013 | 4/5/2013 | 1003 | 4 | Open | |
4/3/2013 | 4/5/2013 | 1004 | 2 | Open | |
4/4/2013 | 4/5/2013 | 1005 | 1 | Open | |
4/4/2013 | 4/5/2013 | 1006 | 1 | Open | |
4/3/2013 | 4/5/2013 | 1007 | 4/4/2013 | 1 | Closed |
Try this
= if(Status='Open',
Count(if([As On Date]-[Commissioning Date]<2 ,[WQMS ID])),
if(Status='Closed',
count(if( [Closure Date]-[Commissioning Date]<2 ,[WQMS ID]))))
Regards,
Kaushik Solanki