Challenge #3: Running Averages
- Suscribirse a un feed RSS
- Marcar tema como nuevo
- Marcar tema como leído
- Flotar este Tema para el usuario actual
- Favorito
- Suscribir
- Silenciar
- Página de impresión sencilla
- Marcar como nuevo
- Favorito
- Suscribir
- Silenciar
- Suscribirse a un feed RSS
- Resaltar
- Imprimir
- Notificar al moderador
We hope you enjoyed last week's challenge. For the third challenge, let’s look at creating 3 and 6 month running averages.
The goal is to create 3 and 6 month running averages for the values contained in columns: c.LK98, p.LK98, c.1K, p.1K, c.NLP3, and p.NLP3. Create the averages by RM Category.
Update: As of 9/20/19, the start and solution files were updated. Your solutions may not look like those posted by Community members prior to this date.
- Etiquetas:
- Advanced
- Data Preparation
- Intermediate
- Join
- Parse
- Preparation
- Transform
- Marcar como nuevo
- Favorito
- Suscribir
- Silenciar
- Suscribirse a un feed RSS
- Resaltar
- Imprimir
- Notificar al moderador
Hi
is this output set correct?
for example row 73 - 6 months averages looks corrupted (not based on HP category)
btw excelent exercise! I really want to see original solution next week.
Thank you
Martin
- Marcar como nuevo
- Favorito
- Suscribir
- Silenciar
- Suscribirse a un feed RSS
- Resaltar
- Imprimir
- Notificar al moderador
Thanks for your week's challenge, but there are some points I have to criticize in your solution:
1. There is a misstake for example on row 139-140/r3mo_d_CAGI there is set a row of "Null" which should be valid data.
The mainproblem is that "Null + x" always returns "Null" as result.
So Null fields have to be replaced with 0 and then reset back to "Null".
This problem occurs in the whole datasheet.
2. In the solution there are a lot of rows set to 0 which should be "Null" for example row 213-216.
I got a solution which hopefully covers those bugs, but it is not a sizeable solution and very much code has to be writen.
Im gonna attach the solution, if you find any misstakes or got a easyer way to solve it, please tell me.
I hope for a sizeable solution, from alteryx, because without I doubt to use it in terms of bussines.
This post has been edited by Community Moderation to redact sensitive attachments. The original attachment has been replaced by post_placeholder.txt.
- Marcar como nuevo
- Favorito
- Suscribir
- Silenciar
- Suscribirse a un feed RSS
- Resaltar
- Imprimir
- Notificar al moderador
Ok, I little bit of brute force but I got there:
This post has been edited by Community Moderation to redact sensitive attachments. The original attachment has been replaced by post_placeholder.txt.
- Marcar como nuevo
- Favorito
- Suscribir
- Silenciar
- Suscribirse a un feed RSS
- Resaltar
- Imprimir
- Notificar al moderador
Hi all,
A lot of similarities in my solution compared to the provided solution - main differences are:
- Using the "Average" function with closest value gives the wrong answer for the second month of every product for the 3month; and for rows 2,3,4,5 for the 6 month
- Only accurate way to do this is to do a multi-row formula to do a row-count; and a multi-row formula to do a sum; and then a formula on each row to work out the average (sum/count)
- I used a formula to rename fields rather than dynamic rename - but I prefer the dynamic rename
Example of the difference:
Average function using multi-row with "Closest value" set to deal with missing rows
Row 1: 1 - 3 month average is 1
Row 2: 2 - correct 3 month average is 1.5 but the "closest value" method gives (1+2+2)/3 instead of (1+2)/2
This post has been edited by Community Moderation to redact sensitive attachments. The original attachment has been replaced by post_placeholder.txt.
- Marcar como nuevo
- Favorito
- Suscribir
- Silenciar
- Suscribirse a un feed RSS
- Resaltar
- Imprimir
- Notificar al moderador
Looks like the sample output has at least 1 truncated column name.
Cheers,
Mark
This post has been edited by Community Moderation to redact sensitive attachments. The original attachment has been replaced by post_placeholder.txt.
Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
- Marcar como nuevo
- Favorito
- Suscribir
- Silenciar
- Suscribirse a un feed RSS
- Resaltar
- Imprimir
- Notificar al moderador
My solution.
This post has been edited by Community Moderation to redact sensitive attachments. The original attachment has been replaced by post_placeholder.txt.
- Marcar como nuevo
- Favorito
- Suscribir
- Silenciar
- Suscribirse a un feed RSS
- Resaltar
- Imprimir
- Notificar al moderador
Challenge #3 complete!
This post has been edited by Community Moderation to redact sensitive attachments. The original attachment has been replaced by post_placeholder.txt.
- Marcar como nuevo
- Favorito
- Suscribir
- Silenciar
- Suscribirse a un feed RSS
- Resaltar
- Imprimir
- Notificar al moderador
My solution is also similar to others
This post has been edited by Community Moderation to redact sensitive attachments. The original attachment has been replaced by post_placeholder.txt.
- Marcar como nuevo
- Favorito
- Suscribir
- Silenciar
- Suscribirse a un feed RSS
- Resaltar
- Imprimir
- Notificar al moderador
Also a similar solution to many above
This post has been edited by Community Moderation to redact sensitive attachments. The original attachment has been replaced by post_placeholder.txt.
- « Anterior
- Siguiente »