SumIf with multiple criteria

[mohdshahtab]

Hello everyone,

I'm new to Alteryx designer and I have a challenge to solve and need your help. 

I have store sales data for multiple stores in two different currencies, I want to sum sales amount following the below criteria:

Sum main and branch-store sales into one line if the value date of both main & branch matches, else return with a comment "value date for main and sub store are different". FYI - There are some stand-alone stores as well with no main store for them. Attached is the file with raw data and desired output. 

Thanks for your help.

Regards,

Shabeer

[geraldo]

@mohdshahtab 

An workflow example

[flying008 · Accepted solution]

Hi, @mohdshahtab 

Another clean way for you reference:

1- Select: Uncheked fields [Sales Total - Local] and [Sales Total - Base] , because it to be sum.
2- Summarize: Group by [Main Store] and [Value Date], Sum of [Sales Total - Local] and [Sales Total - Base], Last of [Store Name].
3- Find&Replace: Find [Store Name] with [Store Name] by 'Entire Field', Append fields with [Sales Total - Local] and [Sales Total - Base].
4- Summarize: Group by [Main Store], Concatenate with [Value Date].
5- Formula: New field [Comments] to get 'Main & Branch store value dates not same'.
6- Find&Replace: Find [Main Store] with [Main Store] by 'Entire Field', Append fields with [Comments].
7- Filter: filter [Sales Total - Local] is not null.

Input
Store ID	Main Store	Store Name	Value Date	Sales Total - Local	Sales Total - Base
9A3EBC2B	F988D312	DollarSmart	3/31/2023	3433	3388.37
D1F92601	F988D312	Megaplex	3/31/2023	2554	2613.76
FC36EDD1	FC36EDD1	Fuller Shelf	3/31/2023	3082	3154.12
EB478028		Organicum	4/30/2023	8796	8681.65
4146AE19		Shopperia	5/31/2023	5175	5296.1
3999CBF0		Freshcommerce	9/30/2022	7834	7732.16
6C655E45	FC36EDD1	Magnolia Market	12/31/2022	7856	7856
F988D312	F988D312	RedFox Shop	3/31/2023	1214	1214
D931335A		Studio Belle	2/28/2023	8158	8051.95
F0D73B28	FC36EDD1	eFarms	1/31/2022	8834	8719.16
AB19ECA1		Vendira	6/30/2023	9051	8933.34
Output
Store ID	Main Store	Store Name	Value Date	Sales Total - Local	Sales Total - Base	Comments
FC36EDD1	FC36EDD1	Fuller Shelf	3/31/2023	3082	3154.12	Main & Branch store value dates not same
EB478028		Organicum	4/30/2023	8796	8681.65
4146AE19		Shopperia	5/31/2023	5175	5296.1
3999CBF0		Freshcommerce	9/30/2022	7834	7732.16
6C655E45	FC36EDD1	Magnolia Market	12/31/2022	7856	7856	Main & Branch store value dates not same
F988D312	F988D312	RedFox Shop	3/31/2023	7201	7216.13
D931335A		Studio Belle	2/28/2023	8158	8051.95
F0D73B28	FC36EDD1	eFarms	1/31/2022	8834	8719.16	Main & Branch store value dates not same
AB19ECA1		Vendira	6/30/2023	9051	8933.34

******

If can help you get your want, please mark it as a solution and give a like to more share.

[mohdshahtab]

Hi @flying008 

Thanks for your reply. Attached is the .xlsx file your reference. I would prefer we consider store ID and not use store name within find and replace tool. 

[flying008 · Accepted solution]

Hi, @mohdshahtab 

[mohdshahtab · Accepted solution]

Hi @flying008 - ID row will then be used to import sales figures into another application, so ID becomes the identifier for the application to identify the store. Thanks!