Date help

[bhavvibudagam]

Hi Experts,

Can any one please help me on below requirement.

From below table

I need to count the IssueNo when Create > DueDate and [Item New String]='Authorised' and [Item Field] ='Staus' in KPi

Please help me to write the expression.

Thanks in advance.

[shruthibk]

if(Created > DueDate,count({<[Item New String]={'Authorised'},[Item Field] ={'Staus'}>}distinct IssueNo))

[YoussefBelloum · Accepted solution]

Hi,

this should solve your problem:

=Count({<IssueNo={"=Created>DueDate"},Item={'Authorized'},[Item Field] ={'Staus'}>}IssueNo)

[vishweshwarisun]

Count(<{Create=">DueDate"},{[Item New String]='Authorised'},{[Item Field] ='Staus'}>IssueNo)

[bhavvibudagam]

Its working Thank you

[prasad_b]

Hi,

Create flag as below

if(CREATED>DUEDATE,'YY','NN') as Flag

and use below expression

=count(DISTINCT {<Flag={'YY'},ITEMNEWSTRING={'Authorised'},ITEMFIELD={'Status'}>}ISSUENO)

[YoussefBelloum]

Create a tableBox and put everything you need, go the LAYOUT section and put a CONDITION DISPLAY

=GetSelectedCount(Dimension_field)

PS: if you click on a value on a table, it will select the Dimension(s) used on that table.

[jubarrosor]

Hello:

In this case I prefer an alternative like calculate a Flag in a script.

I think that is more efficient. For example.

ISSUENO	DUEDATE	CREATED	ITEMNEWSTRING	ITEMFIELD	FLAG
6	8/1/17	08/21/17	Authorised	Status	1
32	6/1/17	06/21/17	Authorised	No Status	0
58	7/12/17	06/22/17	Authorised	Status	0
59	7/13/17	06/23/17	Authorised	Status	0
54	1/13/18	12/24/17	Authorised	Status	0
59	2/8/18	01/19/18	Authorised	Status	0
8	3/25/18	04/14/18	Authorised	Status	1
2	4/1/18	04/21/18	Authorised	Status	0
3	4/2/18	04/22/18	Not Authorised	Status	0
4	4/3/18	04/23/18	Not Authorised	Status	0
5	4/4/18	04/24/18	Not Authorised	Status	0
6	4/5/18	04/25/18	Not Authorised	Status	0

LOAD

     ISSUENO,

     DUEDATE,

     CREATED,

     ITEMNEWSTRING,

     ITEMFIELD,

     IF( CREATE > DUEDATE AND ITEMNEWSTRING='Authorised' AND ITEMFIELD='Status', 1, 0)   AS FLAG

FROM ...;

The KPI in this case is:

     sum(FLAG)

Best Regards,

Juan P.

[YoussefBelloum]

Sorry but I can't follow you anymore..

Can you describe via a picture What is already OK for you and what is the next expected output ?

[YoussefBelloum]

Ok, I see.

You can't type expressions on a tablebox, you will need to use a Straight table for this, use this expression (for example) with all the dimensions you put on the Image below

=Count({<IssueNo={"=Created>DueDate"},Item={'Authorized'},[Item Field] ={'Staus'}>}IssueNo)

and hide this expression on the presentation tab.