No sale on period

[#1]

Hi all,

I try to find a way to analyze my customers which don't have sales depending of a selected period and on two levels (account/brand)

The first step is to analyze by customer => done with this formula

     count({$<key_account = E({<$(sPeriod), key_item={'*'}>})>} distinct key_account))

The second step would be to have the brand which are not bought by an account on the selected period, something like this tab below:

Account - Brand - Flag No sale

I work on this topic since this morning and don't find the solution, the brand column show un-relevant data.

If you have an example to achieved this task, your help will be so much appreciated.

Thks Thibaut

[sunny_talwar]

Would you be able to provide a sample with expected output to be able to help you better?

Best,

Sunny

[#3]

Of course,

Account - Period - Product - Sales

A1 - 201607 - P1 - 10

A2 - 201607 - P2 - 20

A2 - 201607 - P3 - 5

A3 - 201607 - P3 - 30

The output should be for the period 201607 as bellow:

A1 - P2

A1 - P3

A2 - P1

A3 - P1

A3 - P2

Thank you in advance for your help

[sunny_talwar]

I am not sure I understand what you are trying to do here. Would you be able to elaborate a little?

[#5]

Hi Sunny,

I would to have the products which are not bought by an account on a selected period.

With this data in input as example:

key_account - period - key_item - amount

A1 - 201607 - P1 - 10

A2 - 201607 - P2 - 20

A2 - 201607 - P3 - 5

A3 - 201607 - P3 - 30

A4 - null => no data

I would to consolidate by account/product, a no sale KPI.

The output should be as bellow for the period 201607:

A1 - P2 (A1 only bought P1 on 201607)

A1 - P3 (A1 only bought P1 on 201607)

A2 - P1 (A2 bought P2 and P3 on 201607)

A3 - P1 (A3 only bought P3 on 201607)

A3 - P2 (A3 only bought P3 on 201607)

A4 - P1 (A4 no sales on 201607)

A4 - P2 (A4 no sales on 201607)

A4 - P3 (A4 no sales on 201607)

Currently, the KPI works fine if I show only the account in dimension with the formula :

count({$<key_account = E({<$(sPeriod), key_item={'*'}>})>} distinct key_account))

The output for the period is:

A4 (I have no sale on the account A4 for 201607)

Now I just want to have this same result but with the product dimension.

I find a lot of no sale KPI examples but never find the way to consolidate by products.

Thank you in advance for your help.

[varshavig12]

I'm not sure what exactly you are looking for, but based on your sample expected output

Is this okay ?

I think this is not the correct way. But still as a workaround it can be used.

[#7]

Hi,

Unfortunately, I already tried this example   but I need to have the product with no concatenation, for a customer with just one product sold in a period, I need to display all other products, the concatenation would be unreadable.

Thank you for your help!

[MayilVahanan]

Hi

Its more or less  varshavig12 approach. But in back end.. 

The front end solution is not suite exactly because, If the user select Account, then the values become null ..

Account	Product
A1

Try like this

Temp:

LOAD *,Account &'_'&Period  AS Key INLINE [

    Account , Period , Product , Sales  

    A1 , 201607 , P1 , 10  

    A2 , 201607 , P2 , 20  

    A2 , 201607 , P3 , 5  

    A3 , 201607 , P3 , 30

];

Join

LOAD Distinct Period, Product as Prd Resident Temp;

ExcludeProd:

LOAD Key, SubField(Trim(Replace(AllProd, OriginalPrd,' ')),' ') as ExcludeProd;

LOAD Distinct Key , Concat(Distinct Prd,' ') as AllProd, Concat(Distinct Product, ' ') as OriginalPrd Resident Temp Group by Key;

Output:

Account	ExcludeProd	1
A1	P2	1
	P3	1
A2	P1	1
A3	P1	1
	P2	1

[#9]

Hi

I already thought to calculate the no sale data directly in the model but I though it was not the right approach in term of data volume.

Ok, if nobody have the solution with expressions. I will done this topic by data modeling.

Thank you!