Skip to main content
Announcements
See why Qlik was named a Leader in the 2024 Gartner® Magic Quadrant™ for Data Integration Tools for the ninth year in a row: Get the report
cancel
Showing results for 
Search instead for 
Did you mean: 
bhavvibudagam
Creator II
Creator II

Date help

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.

1 Solution

Accepted Solutions
YoussefBelloum
Champion
Champion

Hi,

this should solve your problem:

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

View solution in original post

10 Replies
shruthibk
Creator
Creator

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

YoussefBelloum
Champion
Champion

Hi,

this should solve your problem:

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

vishweshwarisun
Partner - Creator
Partner - Creator

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

bhavvibudagam
Creator II
Creator II
Author

Its working Thank you

prasad_b
Partner - Contributor
Partner - Contributor

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

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

Hello:

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

I think that is more efficient. For example.

      

ISSUENODUEDATECREATEDITEMNEWSTRINGITEMFIELDFLAG
68/1/1708/21/17AuthorisedStatus1
326/1/1706/21/17AuthorisedNo Status0
587/12/1706/22/17AuthorisedStatus0
597/13/1706/23/17AuthorisedStatus0
541/13/1812/24/17AuthorisedStatus0
592/8/1801/19/18AuthorisedStatus0
83/25/1804/14/18AuthorisedStatus1
24/1/1804/21/18AuthorisedStatus0
34/2/1804/22/18Not AuthorisedStatus0
44/3/1804/23/18Not AuthorisedStatus0
54/4/1804/24/18Not AuthorisedStatus0
64/5/1804/25/18Not AuthorisedStatus0

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

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

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.