Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

bhavvibudagam
Contributor 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
Esteemed Contributor

Re: Date help

Hi,

this should solve your problem:

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

10 Replies
shruthibk
Contributor

Re: Date help

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

YoussefBelloum
Esteemed Contributor

Re: Date help

Hi,

this should solve your problem:

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

vishweshwarisun
Contributor

Re: Date help

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

bhavvibudagam
Contributor II

Re: Date help

Its working Thank you

prasad_b
New Contributor

Re: Date help

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
Esteemed Contributor

Re: Date help

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.

jubarros
Contributor II

Re: Date help

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
Esteemed Contributor

Re: Date help

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
Esteemed Contributor

Re: Date help

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.

Community Browser