Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BI & Data Trends 2021. Discover the top 10 trends emerging in today. Join us on Dec. 8th REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

Set Analysis

HI,

I'm trying to add a condition to set analysis.

The final result will display a list of customers that don't have sales on selected months 

For example, customer "A" have sales in November and in June 2020.

so when the user selects the months of November or June this customer should not be displayed because of the sales he has on those months.

but if the customer selects another month this customer will be displayed (if he selects March the customer will be displayed).

also if the customer doesn't select a month the customer won't be displayed because he has sales on two months in the selected year. 

I'm using the following set analysis:

if(sum(${<[Trans Sales Flag]={'N'},[Month]-={$(vListOfSalesMonths)})>}[Trans Revenue ])=0,[Variety])

*vListOfSalesMonths=

=concat( distinct chr(39) & [Month],chr(39) &',') & chr(39)

I’m having trouble with the month condition.

Thanks,

 

Labels (3)
4 Replies
Highlighted
Master
Master

@bellesol  One Option:

1-

I created a new variable

vListOfSalesCustomer==concat( distinct chr(39) & customer,chr(39) &',') & chr(39)

Then in chart

Dimension : Customer

and Measure like :

sum( {1<[Trans Sales Flag]={'N'},customer = E({1<customer={$(vListOfSalesCustomer)}>})>} Sales )

for this input :

Capture.PNG

If I selected 04/2020

Capture.PNG

 

or

Capture.PNG

 

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
Highlighted
Creator
Creator

Hi,

Thanks a lot, I have another related question,

assume the model has two tables:

1."customer"

2. "Customer Order"

The question is: If the customer started to exist in the table "customer order" on 2.2020,

so in the model, the selection of "Januar" for this customer doesn't exist because the data of the table is only taken from table "customer order"

how can I display this customer (when the user selects "January") even though he doesn't exist in the orders table? because the table I want to display in the dashboard display customers that don't have sales 

Highlighted
Master
Master

@bellesol  can you share a sample data and the expected output ? can we change in load Script (add flag..)?

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
Highlighted
Creator
Creator

It's a problem to add sample

but maybe you can help me with the final calculation:
a remainder - the table display all customer that didn't commit a sale.

if(sum({<[Sales Flag]={'Y'}>}[Sales])=0

or

sum({1<[Pay Customer Code] = E({1<[Sales Flag]={'Y'}>})>} [Sales])>=0

the first calculation display all the customer that the sum of sales is "0", mining they didn't do a sale on the selected date

and in the second calculation, I need to exclude customers that the sum of sales is bigger and equal to zero, maybe this is the way to display null value?