Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

Sales Comparison with LY and LLY

ash25sumbre
8 - Asteroid

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 

5 REPLIES 5
apathetichell
16 - Nebula

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.

ash25sumbre
8 - Asteroid

@apathetichell 

I've attached data for reference. What you said is confusing can you please elaborate . It will help me to understand.

 

Regards,

Aishwarya

 

RolandSchubert
15 - Aurora
15 - Aurora

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.

 

2021-09-20_07-57-48.jpg

 

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

 

Best,

 

Roland

ash25sumbre
8 - Asteroid

@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.

ash25sumbre_0-1632207104724.png

 

Regards,

Aishwarya

RolandSchubert
15 - Aurora
15 - Aurora

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

 

Labels