SumIf with multiple criteria

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

Store Transactions.xlsx

Datasets

Help

Accepted answers

flying008

Hi, @mohdshahtab

Another clean way for you reference:

录制_2023_07_17_10_17_26_214.gif

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.

flying008

Hi, @mohdshahtab

录制_2023_08_25_14_52_03_896.gif

mohdshahtab

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!

All comments

An workflow example

Hi, @mohdshahtab

Another clean way for you reference:

录制_2023_07_17_10_17_26_214.gif

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.

Monthly Revenue by Store Location.xlsx

flying008

Hi, @mohdshahtab

录制_2023_08_25_14_52_03_896.gif

mohdshahtab

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!

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

RithiS 280