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
extract month and year from your specific dates (cant' tell you exactly how without seeing your date format). Cross tab - month is key column - year becomes column, sales become value.
I've attached data for reference. What you said is confusing can you please elaborate . It will help me to understand.
Regards,
Aishwarya
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
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
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