LAG and grouping

[nidah5]

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

[atcodedog05 · Accepted solution]

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 😀👍

[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:

[Qiu]

@nidah5 
Check if this is what you need

Spoiler

[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!

[grazitti_sapna]

Hi @nidah5, give this a try.

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

Thanks.

[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