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

Data Splitting

Mihail
5 - Atom

Hi all,

 

My question is quite simple, but I have not been able to find the answer in the discussion. I have sales data from the beginning of 2017 up until today's date and I would like to split it into TY and LY. I am trying to eliminate all the dates from 2017 and to be left with 4 columns: Date - starting from 01/01/2018; Department; Sales TY - sales per day this year; Sales LY - the daily sales from 2017 displayed next to the corresponding daily sales from TY. I am trying to do this as it will be easier to later analyse the data in Tableau. I have found this task a bit tricky as some departments aren't reported each day in both years and some of them have closed (not reported anymore OR still being reported as having 0 sales).

 

Thank you in advance :).

 

Mihail

6 REPLIES 6
BenMoss
ACE Emeritus
ACE Emeritus

Can you give some sample data, otherwise it's very difficult to truly understand and tell you how to solve the problem.

 

Ben

Mihail
5 - Atom

Hi Ben and thanks for the quick reply.

 

The file titled "example" is a piece of the raw data. "Solution" is how I would like the data to look like, where Sales TY are the actual sales on the specified date and Sales LY are the sales that happened exactly one year ago on that day (in the same departments and brand ID). My main problem is that when blending Alteryx excludes the data for the departments that are not reported anymore/don't have a corresponding record for TY or LY.

 

My workflow looks like this (I have selected the place where I attempt to split the data and fail :D): 

 

 

Thanks a lot :)Untitled.jpg

ramatp30
7 - Meteor

HI @Mihail

 

As per my understanding attached workflow should work for your use case

 

Please try and let me know.

 

Thanks,

Ramana

Mihail
5 - Atom

Hi Ramana,

 

It does work for the most part, but it still excludes some of the departments that are not being reported anymore. I am aiming to still have them and if there is no corresponding record for them in TY or LY to create a cell with "0" in that column. 

Example: if Dept 97 was not been reported in 2018 I would like to have its record in LY and to have "0" in the same row in TY.

 

Thanks a lot :)

ramatp30
7 - Meteor

Hi @Mihail

 

This should work for your use case.

 

Let me know if you have any questions.

CoreyB
5 - Atom

I can understand using true/false when you only have two years, but how would you approach this if there were three years of data.  Would you have to stack 2 filters sequentially?

 

Labels