Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in Toronto Sept 9th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Set Expression

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

4 Replies
ecolomer
Master II
Master II

see this documents

hic
Former Employee
Former Employee

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
Partner - Champion III
Partner - Champion III

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

  , ','

)

Not applicable
Author

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