topic Re: Need help with set ananlysis in QlikView

Need help with set ananlysis

Tue, 11 Feb 2014 11:05:52 GMT

Hi All,

i have a straigth table, and i want to show revenue of lost clients ( ho by in 2012, but not in buy 2013) and new clients (ho buy in 2013 but not by in 2012)

my revenue set analysis is : sum({$<Invoice.InvoiceStatus = {'$(vInvoiceApprove)'}>} Invoice.RevenueLT/Currency.Rate)

please help

Re: Need help with set ananlysis

Sokkorn — Tue, 11 Feb 2014 11:07:46 GMT

Hi,

What is definition of vInvoiceApprove?

Regards,

Sokkorn

Re: Need help with set ananlysis

Sokkorn — Tue, 11 Feb 2014 11:08:05 GMT

Can you share your app?

Re: Need help with set ananlysis

Tue, 11 Feb 2014 11:14:54 GMT

Attached to my first post

Re: Need help with set ananlysis

Sokkorn — Wed, 19 Feb 2014 01:06:06 GMT

Hi Sigitas,

I think you should have customer status in your DB.

Here is some sample expression:

1. Lost customer during 2012 = sum({$<Invoice.InvoiceStatus = {'Patvirtinta'}, Year = {2012}>} Invoice.RevenueLT/Currency.Rate)

2. Lost customer during 2013 = sum({$<Invoice.InvoiceStatus = {'Patvirtinta'}, Year = {2013}>} Invoice.RevenueLT/Currency.Rate)

Can you share condition how to define New/Lost customer?

Regards,

Sokkorn

Re: Need help with set ananlysis

Wed, 19 Feb 2014 07:40:55 GMT

Hi, i solved my issue by this expresion, for new customer (during 2013):

sum({$<HalfYear, Quarter, Month, Year={$(=max(Year)-1)},

Customer.Customer =

P({$<HalfYear, Quarter, Month, Year={$(=max(Year)-1)}>})

* E({$<HalfYear, Quarter, Month, Year={2012,2011,2010,2009,2008,2007,2006}>})

,Invoice.InvoiceStatus = {'$(vInvoiceApprove)'} >} Invoice.RevenueLT)

for lost customer (during 2013):

sum({$<HalfYear, Quarter, Month, Year={$(=max(Year)-2)},

Customer.Customer =

P({$<HalfYear, Quarter, Month, Year={$(=max(Year)-2)}>})

* E({$<HalfYear, Quarter, Month, Year={$(=max(Year)-1)}>}),Invoice.InvoiceStatus = {'$(vInvoiceApprove)'}>} Invoice.RevenueLT/Currency.Rate)