Need help with set ananlysis

[#1]

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

[Sokkorn]

Hi,

What is definition of vInvoiceApprove?

Regards,

Sokkorn

[Sokkorn]

Can you share your app?

[#4]

Attached to my first post

[Sokkorn]

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

[#6]

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)