Set Expression

[#1]

Hi All,

Can any one help me on how to write a set expression for, the customers who are having sales amount 0 in the last year and above 0 in the Current Year. ,

Regards

John

[ecolomer]

see this documents

[hic]

Assuming that you have current year and last year in variables, you could do the following:

Sum({$<Customer={"=Sum({1<Year={"$(vLastYear)"}>} Sales)=0 and Sum({1<Year={"$(vCurrentYear)"}>}Sales)>0"}>} Sales)

In other words: An expression search where the conditions are defined through Set Analysis. The outer Sum( ... Sales) can of course be replace by another appropriate aggregation. See also The Expression Search

HIC

[petter]

Using element functions ...

=Concat( DISTINCT 

{

1 < CustomerID=P( {1<ID={"=Sum(Sales)=0"},Year={$(=Year(Today())-1)}>} ) >

*

1 < CustomerID=P( {1<ID={"=Sum(Sales)>0"},Year={$(=Year(Today()))}>} ) >

}

  Customer

  , ','

)

[#5]

Hi,

Add the following expression to any chart as expression:

Sum({$<Customer={"=Sum({1<Year={$(=Year(Today()) - 1)}>} [Sold $])=0 and Sum({1<Year={$(=Year(Today()))}>} [Sold $])>0"}>} [Sold $])

Here:

Customer: is your field which you check for no sales last year but this year (so it is new customer/business)

Year: is the year field in the model

Sold $: is the metric/KPI which you want to check. That is probably Sales

You might achieve similar results in many different ways. If it is just selecting Customers/IDs Petter's post help.

Best,

Ersen