Sales Comparison with LY and LLY

Question

Hello Everyone ,

I'm working on sales data which having data from 2019 to 2021 on daily basis.

here I 'm stuck at following problems .

1. How I can compare sales of this year with previous year and LLY.

eg.  I want to check sales on 26-04-2021 is higher than sales on same day of LY and LLY .

2. Also want to check  how much increase/decrease in % of sales compare to LY and LLY.

2. Also using summarize tool  ,I'm getting  total sum of sales corresponds to 2019,2020 and 2021  on monthly basis, quaterly basis, yearly basis. Now want to compare these values how shall  proceed ??

eg. I want to compare  April 2021 sales with April 2020 sales and April 2019 Sales  .

I've tried to transpose tool after summarize tool but not getting any day after running .

Please give some ideas , It will help me a lot.

Regards ,

Aishwarya

Sales files to compare.csv

RolandSchubert · Answer

Hi @ash25sumbre ,

you can do this using a Formula tool and a condition, e.g.:

I would check, if both values (Sales and Sales_LY are available), then you can easily compare the columns and set the flag, if sales lt sales ly.

IF !IsNull([Sales]) AND !IsNull([Sales_LY]) THEN
IF [Sales] < [Sales_LY] THEN
1
ELSE
0
ENDIF
ELSE
Null()
ENDIF

I've added the formula tool to the workflow. btw: You can compare monthly values (or whatever aggregation you create)  the same way.

Best,

Roland

SalesLY_LLY.yxmd

ash25sumbre · Answer

@RolandSchubert

Thanks for help .

Also I want to check for daily sales ,example- the sales on 9-07-2021  compare to sales in 2020  , if sales is less than LY sales then its "1" else "0" . I want this idea for all data . will you please share ideas how I can do that ??

Also after using the method you mentioned why I'm getting null values for last year sales after using summarize tool. I've attached screenshot  for same.

Regards,

Aishwarya

RolandSchubert · Answer

Hi @ash25sumbre,

you can use DateTime functions. First you'll have to convert the date to a date data type (DateTimeParse), then you can use DateTimeAdd to find the dates for LY and LLY (if you want the same date in LY you can use DateTimeAdd([Date], -1, 'year').

You can use a Join tool to find the respective Dates in LY / LLY and a Summarize tool to calculate month comparison.

I've added a sample workflow, let me know if it works for you.

Best,

Roland

SalesLY_LLY.yxzp