LAG and grouping

Hi,

I have data set, where one column is CUSTOMER and other is REVENUE & third is DATE.

i want to convert the date to sort it in order, then compare quarterly revenue of a particular Customer.

i am not sure, how exactly should i group it into quarters and then compare.

should i group it and use lag function to compare?

Here is the sample data

CUSTOMER	REVENUE	DATE
ACSS	23444	Apr- 19
ACSS	34221	Aug-18
ACSS	45523	Jun-19
DELI	982762	Aug-19
DELI	376910	Dec-18
DELI	163791	Apr-19

Datasets

Accepted answers

atcodedog05

Hi @nidah5 ,

Here is the workflow for the task

Input: table given by you

Output:

Quarterly

Monthly

Workflow:

Hope this meet the requirement.

Interactive lesson on Multi - Row formula https://community.alteryx.com/t5/Interactive-Lessons/Multi-Row-Formula/ta-p/82872

Hope this helps 🙂

If this post helps you please mark it as solution. And give a like if you dont mind 😀👍

Quarter.yxmd

All comments

atcodedog05

Hi @nidah5 ,

Here is the workflow for the task

Input: table given by you

Output:

Quarterly

Monthly

Workflow:

Hope this meet the requirement.

Interactive lesson on Multi - Row formula https://community.alteryx.com/t5/Interactive-Lessons/Multi-Row-Formula/ta-p/82872

Hope this helps 🙂

If this post helps you please mark it as solution. And give a like if you dont mind 😀👍

Quarter.yxmd

AbhilashR

Hi @nidah5, I have included a solution that converts your date string into an Alteryx date format after which you can sort your dataset and analyze further:

sample.yxmd

Qiu

@nidah5
Check if this is what you need

Spoiler

1006-nidah5.yxmd

Harbinger

Hey @nidah5,

This is a great question! It depends on what you want to do with the data. regardless, it sounds like you're going to want to convert the string date to a data data type and then get your quarter from it.

To get the date data type, use the data time parse. Then you can get the quarter with the following clever little formula: "Q"+tostring(Ceil(ToNumber(DateTimeFormat([Date],'%m'))/3)). This will yield Q1, Q2, Q3, or Q4. You can now sort your data by Company and then date.

From here, the world is yours! You can pivot the data to get the revenue by company per quarter in columns. Or you can use the multi-row formula to compare growth. What you do next is entirely dependent on how you'd like to analyze the data!

Pleas let me know if you have questions!

645193 LAG and Grouping.yxmd

grazitti_sapna

Hi @nidah5, give this a try.

I have sorted it on the basis of customer name and date by ascending.

Thanks.

Lag-Grouping-GS.yxmd

atcodedog05

Happy to help 🙂

Cheers and happy analyzing 😀

nidah5

now if i have similar data set with extra column as COMPLAINTS, showing the number of complaints raised by each customer and i want to compare the current month of revenue with lag value of complaint 3 months prior. like -3.

i have attached the workflow , may i know how to find the pearson correlation for same on each account

alteryx_lag_corr.yxmd

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

RithiS 280