Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have the below data. What I'm looking for is a way to get the Difference column.
I have gotten the avg by cycle column by using this formula where Max Days is the latest date in each cycle.
if(day(DATE)=MaxDays,aggr(nodistinct Avg({$<LABEL={'First'}>} PRICE),CURVE,CYCLE),0)
CURVE | CYCLE | LABEL | DATE | PRICE | Avg by Cycle | Difference |
Apples | 1 | first | 1/1/2020 | $ 1.00 | ||
Apples | 1 | first | 1/2/2020 | $ 2.00 | ||
Apples | 1 | first | 1/3/2020 | $ 4.00 | ||
Apples | 1 | first | 1/4/2020 | $ 2.50 | ||
Apples | 1 | first | 1/5/2020 | $ 1.50 | ||
Apples | 1 | first | 1/6/2020 | $ 4.00 | ||
Apples | 1 | first | 1/7/2020 | $ 3.20 | ||
Apples | 1 | first | 1/8/2020 | $ 2.40 | ||
Apples | 1 | first | 1/9/2020 | $ 5.10 | ||
Apples | 1 | first | 1/10/2020 | $ 1.60 | $ 2.73 | |
Apples | 2 | first | 1/11/2020 | $ 2.50 | ||
Apples | 2 | first | 1/12/2020 | $ 4.50 | ||
Apples | 2 | first | 1/13/2020 | $ 4.10 | ||
Apples | 2 | first | 1/14/2020 | $ 4.20 | ||
Apples | 2 | first | 1/15/2020 | $ 2.34 | ||
Apples | 2 | first | 1/16/2020 | $ 2.53 | ||
Apples | 2 | first | 1/17/2020 | $ 5.24 | ||
Apples | 2 | first | 1/18/2020 | $ 1.24 | ||
Apples | 2 | first | 1/19/2020 | $ 2.52 | ||
Apples | 2 | first | 1/20/2020 | $ 1.65 | $ 3.08 | $ 0.35 |
Apples | 3 | first | 1/21/2020 | $ 2.41 | ||
Apples | 3 | first | 1/22/2020 | $ 2.54 | ||
Apples | 3 | first | 1/23/2020 | $ 1.52 | ||
Apples | 3 | first | 1/24/2020 | $ 1.52 | ||
Apples | 3 | first | 1/25/2020 | $ 4.32 | ||
Apples | 3 | first | 1/26/2020 | $ 5.12 | ||
Apples | 3 | first | 1/27/2020 | $ 3.12 | ||
Apples | 3 | first | 1/28/2020 | $ 4.13 | ||
Apples | 3 | first | 1/29/2020 | $ 2.14 | ||
Apples | 3 | first | 1/30/2020 | $ 2.10 | ||
Apples | 3 | first | 1/31/2020 | $ 1.00 | $ 2.72 | $ (0.36) |
Is there a way to get the difference column to take the difference between the average of the current cycle minus average of the previous cycle. I've tried above functions but I haven't found a way to make it work.
@sarahshong try below expressions
// Avg. by Cycle
=if(DATE =max(TOTAL <CURVE,CYCLE>{$<LABEL={'First'}>} DATE),
Avg(TOTAL<CURVE,CYCLE>{$<LABEL={'First'}>} PRICE))
// Difference
= if(DATE =max(TOTAL <CURVE,CYCLE>{$<LABEL={'First'}>} DATE),
Avg(TOTAL<CURVE,CYCLE>{$<LABEL={'First'}>} PRICE)-aggr(NODISTINCT above(Avg(TOTAL<CURVE,CYCLE>{$<LABEL={'First'}>} PRICE)),CURVE,CYCLE))
Sort the chart in below order
1) CURVE
2) CYCLE
3) DATE
Uncheck "Suppress zero-values" option from presentation tab properties
@sarahshong Please share more sample data as it will be hard to tell where is the gap. you have used set analysis for cycle. Based on your problem, I can see that you don't really need that set analysis. So remove that set analysis wherever it is used and try.
@sarahshong try below expressions
// Avg. by Cycle
=if(DATE =max(TOTAL <CURVE,CYCLE>{$<LABEL={'First'}>} DATE),
Avg(TOTAL<CURVE,CYCLE>{$<LABEL={'First'}>} PRICE))
// Difference
= if(DATE =max(TOTAL <CURVE,CYCLE>{$<LABEL={'First'}>} DATE),
Avg(TOTAL<CURVE,CYCLE>{$<LABEL={'First'}>} PRICE)-aggr(NODISTINCT above(Avg(TOTAL<CURVE,CYCLE>{$<LABEL={'First'}>} PRICE)),CURVE,CYCLE))
Sort the chart in below order
1) CURVE
2) CYCLE
3) DATE
Uncheck "Suppress zero-values" option from presentation tab properties
Thank you for your reply. When I applied to my data I'm not getting what is expected. It worked for the second cycle but for the third cycle it came up null. For the first cycle it took the difference between the avg by cycle on the 10th and 31st.
It seems to be the second part of the expression that is creating this shift. but can't really figure out why. any suggestions? Could it have something to do with how my fields are sorted?
aggr(NODISTINCT above(Avg(TOTAL<CURVE,CYCLE>{$<LABEL={'First'}>} PRICE)),CURVE,CYCLE))
@sarahshong Please share more sample data as it will be hard to tell where is the gap. you have used set analysis for cycle. Based on your problem, I can see that you don't really need that set analysis. So remove that set analysis wherever it is used and try.