Set Analysis

[bellesol]

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,

[Taoufiq_Zarra]

@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 :

If I selected 04/2020

or

[bellesol]

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 

[Taoufiq_Zarra]

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

[bellesol]

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?