Skip to main content
Announcements
Qlik Connect 2025: 3 days of full immersion in data, analytics, and AI. May 13-15 | Orlando, FL: Learn More
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Case or Set Analysis

Hi!

I try to create the Expression below with set analysis:

count({$<Year([In_Order Date])={2012}>} distinct InvoiceNo)

I want to count InvoiceNr if the Order Date (In_Order Date) is 2012 something ( I would like to do between 0 and 12 month agoo but I dont kniow how)

--> There is an error in the expression.

I could also use "cases" in the skript:

my cases would be:

In_Order Date last 12 month (calles Active)

In_Order Date between 13-36 month ago (called Passive)

In_Order Date older than 36 month ago (called Passive_old)

In_Order Date does not exist - (called No)

How do I do this?

/Julia

1 Solution

Accepted Solutions
Gysbert_Wassenaar

Try this to get a distinct count of InvoiceNo where [In_Order Date] lies in 2012:

count({$<[In_Order Date]={'=Year([In_Order Date])=2012'}>} distinct InvoiceNo)

For the script you can try this:

if([In_Order Date]> addyears(today(),-1) and [In_Order Date] <= today(),'Active',

    if([In_Order Date]<= addyears(today(),-1) and [In_Order Date]> addyears(today(),-2), 'Passive',

        if([In_Order Date]<= addyears(today(),-2), 'Passive_Old',

            if(len(trim([In_Order Date]))=0, 'No'))))


talk is cheap, supply exceeds demand

View solution in original post

2 Replies
Gysbert_Wassenaar

Try this to get a distinct count of InvoiceNo where [In_Order Date] lies in 2012:

count({$<[In_Order Date]={'=Year([In_Order Date])=2012'}>} distinct InvoiceNo)

For the script you can try this:

if([In_Order Date]> addyears(today(),-1) and [In_Order Date] <= today(),'Active',

    if([In_Order Date]<= addyears(today(),-1) and [In_Order Date]> addyears(today(),-2), 'Passive',

        if([In_Order Date]<= addyears(today(),-2), 'Passive_Old',

            if(len(trim([In_Order Date]))=0, 'No'))))


talk is cheap, supply exceeds demand
Not applicable
Author

Thank you! It worked!

How do I do it if I want between the interval 13-36 month ago.

when n_Order Date is defined as YY-MM-DD

?

/Julia