Replace sales if there's no data in that quarter between the same seller/buyer

[Joel_Holmberg]

Hello,

I understand that the subject of my problem might be confusing, so please let me try to explain.
I'm in dire need of help with a set analysis for my application.

I've got two kinds of sales, let's call them A & B.
Both A & B is sold and bought by the same suppliers and customers.
A is data I only get once a quarter and B is data I get every day.

So what I want to do is do a set analysis which produce the following;

If there's data between Customer X and Supplier Y in sales type A in a quarter, it should print 0, no matter what B is.
If there's no data between X and Y in sales type A in a quarter, I want it to print B.
This also needs to work if I select an entire year and not just a single quarter.

If I just do this with an if-statement, it works in a table with all data but I need to work with graphs and KPI's too.

All help is very much appriciated, thank you.

[sunny_talwar]

It might be easier if you can share some sample data and let us know the output you expect to see from the sample data provided.

[Joel_Holmberg]

Of course! Sorry!

Sales, Customer, Supplier, Quarter, Amount
A,X,Y,Q1,10
A,X,Y,Q2,0
B,X,Y,Q1,5
B,X,Y,Q2,5

Output;

Sales Q1 = 10.
Sales Q2 = 5.

There are only two types of sales (A & B) but multiple kinds of suppliers/customers.

[sunny_talwar · Accepted solution]

One way is to do this in the script to create flags of which data would you want to pick.... but that would need changes in the script... if those are okay, I would def. go with this solution... else you can try this

Sum(Aggr(
If(Sum({<Sales = {'A'}>}Amount) > 0, Sum({<Sales = {'A'}>}Amount), Sum({<Sales = {'B'}>}Amount))
, Customer, Supplier, Quarter))

[Joel_Holmberg]

Hello,

Just wanted to say it worked exactly how I wanted it to.

Thank you very much & have a great week!

[sunny_talwar]

Thanks Joel!! You have a great week yourself my friend.