Alteryx Designer Desktop Discussions

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

Calculate Current row + previous row then next to following ones without double counting

Tmanuela
8 - Asteroid

Hi community,

 

I try to calculate (might seem) something simple : I have a data set with readings for each 15mins intervals throughout a day, so I need then in the end calculated at 30 mins interval, this means :

00:00 = 00:00 + 23:45 (previous day)

00:30 = 00:15 + 00:30 

01:00 = 00:45 + 01:00

so on. without double counting / summing the rows, however we can do this and then filter out the unnecessary rows. It seems tricky for me to find out how to take the 23:45 from prev day..

 

I did showed pictures before and after cross tab in order to see how I need data in the end as after cross tab but I think the multi row formula must be placed before. 

 

Also attached the dataset.before.JPG

multirow.JPG

Thank you

M.

9 REPLIES 9
atcodedog05
22 - Nova
22 - Nova

Hi @Tmanuela 

 

Can you please provide your data file its really hard to work with out that.

atcodedog05
22 - Nova
22 - Nova

@Tmanuela 

 

So currently you have 15mins interval it needs to be clubbed to 30 mins interval is that right.

 

If you can give like 10 rows of data will also do.

Tmanuela
8 - Asteroid

@atcodedog05 that is correct, 

 

my data attached with my alteryx workflow so no need to start from zero. 

atcodedog05
22 - Nova
22 - Nova

Hi 

 

Here is a workflow for the task.

Output: Column New code

atcodedog05_0-1604484646770.png

Workflow:

atcodedog05_1-1604484678409.png

The highlighted Mutil-Row formula is the tool generating the new column.

 

Hope this helps 🙂 Please check and let me know.


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

Tmanuela
8 - Asteroid

Sorry...I forgot to mention when I mentioned this calculations 

00:30 = 00:15 + 00:30 

01:00 = 00:45 + 01:00

I meant to sum the readings so if the 00:15 reading = 35 and 00:30 = 20, the reading for 30 mins at 00:30 = 35+20 if this makes sense...

the output I need to be something like this made manually by me in Excel :5.JPG

atcodedog05
22 - Nova
22 - Nova

Hi @Tmanuela 

 

before crosstab i have changed reading to double and in crosstab i have set to sum like below.

atcodedog05_0-1604485810023.png

So what you are saying should be working please check few outputs and let me know🙂

Tmanuela
8 - Asteroid

@atcodedog05 sorry but don't quite understand how did you changed reading to double...? in the multi row formula...you mean you changed the [Code] variable to [Readings]?

atcodedog05
22 - Nova
22 - Nova

@Tmanuela 

 

you have a select tool before multi-row tool i changed it over there.

 

Readings to numeric datatype

atcodedog05
22 - Nova
22 - Nova

Happy to help 🙂 @Tmanuela 

 

Cheers and Happy Analyzing 😀

 

Feel free to reach out if you face any issues 🙂

Labels