Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

LAG and grouping

nidah5
8 - Asteroid

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

CUSTOMERREVENUEDATE
ACSS23444Apr- 19
ACSS34221Aug-18
ACSS45523Jun-19
DELI982762Aug-19
DELI376910Dec-18
DELI163791Apr-19
7 REPLIES 7
atcodedog05
22 - Nova
22 - Nova

Hi @nidah5 ,

 

Here is the workflow for the task

 

Input: table given by you

Output:

Quarterly

atcodedog05_0-1601953651311.png

Monthly

atcodedog05_0-1601953938097.png

 

Workflow:

atcodedog05_1-1601953952685.png

 

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
15 - Aurora
15 - Aurora

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:

AbhilashR_0-1601953907066.png

 

 

Qiu
20 - Arcturus
20 - Arcturus

@nidah5 
Check if this is what you need

Spoiler
Capture123.PNG
Harbinger
9 - Comet

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
17 - Castor

Hi @nidah5, give this a try.

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

grazitti_sapna_0-1601958985042.png

 

Thanks.

 

Sapna Gupta
atcodedog05
22 - Nova
22 - Nova

Happy to help 🙂

 

Cheers and happy analyzing 😀

nidah5
8 - Asteroid

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

Labels