topic Re: Case or Set Analysis in QlikView

Case or Set Analysis

Mon, 26 Nov 2012 14:23:15 GMT

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

Gysbert_Wassenaar — Mon, 26 Nov 2012 14:32:15 GMT

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'))))

Mon, 26 Nov 2012 14:45:58 GMT

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