topic Re: Set Expression in QlikView

Set Expression

Fri, 25 Sep 2015 18:42:14 GMT

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

Re: Set Expression

ecolomer — Fri, 25 Sep 2015 21:02:12 GMT

see this documents

Re: Set Expression

hic — Fri, 25 Sep 2015 21:41:27 GMT

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

Re: Set Expression

petter — Fri, 25 Sep 2015 21:49:05 GMT

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

, ','

)

Re: Set Expression

Fri, 25 Sep 2015 22:24:56 GMT

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