Skip to main content
Announcements
Save $600 on Qlik Connect registration! Sign up by Dec. 6 to get an extra $100 off with code CYBERSAVE: REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
gandekoutilya
Contributor
Contributor

Script for Count of Records using Multiple if between dates

Hi Hi

3 Replies
vamsee
Specialist
Specialist

Try

Load

TextCount(If([Current Status] = 'Filled', [Requisition Filled Date] >= MakeDate(2018,8,1), [Aged Requisitions])) as [Filled]

Also, In the discussion, you have not mentioned the other columns and Groupby. TextCount() requires groupby.

gandekoutilya
Contributor
Contributor
Author

I actually want a script to know the count of aged requisitions for this two condition: Current Status=Filled and Requisition Filled Date from 1/1/2018

Aged Requisitions, Current Status, Requisition filled Date are the columns

vamsee
Specialist
Specialist

Try doing it the other way round then.

For Example:

Source:

LOAD * INLINE [

    Aged Requisitions, Current Status, Requisition Filled Date

    A, Filled, 9/17/2017

    B, Not Filled, 9/18/2018

    C, Filled, 9/19/2017

    A, Filled, 9/20/2018

    B, Not Filled, 9/21/2017

    C, Filled, 9/22/2018

    A, Not Filled, 9/23/2018

    B, Filled, 9/24/2018

    C, Filled, 9/25/2018

    A, Filled, 9/26/2017

    B, Filled, 9/27/2018

    C, Filled, 9/28/2018

    A, Filled, 9/29/2018

    B, Not Filled, 9/30/2018

];

B:

LOAD

[Aged Requisitions],

Count( Distinct RecNo()) as Count1

Resident Source

Where

[Current Status] = 'Filled'

and [Requisition Filled Date] >= Date(YearStart(Today()))

Group By [Aged Requisitions];

DROP Table Source;

Let me know if this works for you.