How to make the average of the previous month minus the average of the current month?

[July]

Hi guys,

How to make a calculating field that calculates:

media from the field where the month is the previous one, such as:

avg(my_value) , my_date = previous_month

then, media from the field where the month is the current one, such as:

avg(my_value) , my_date = this_month

I will use these two fields to make another calculated field to calculate the difference later.

PS: I could´nt do it with periodOverPeriodPercentDifference because I need the average of the average

Thanks!

[July]

I tried this way, but it’s not working

ifelse({month_current} = ‘1’, avgOver(avg({limit_p}), [{id_customer}]) - ifelse ({month_previous} = 1, avgOver(avg({limit_p}), [{id_customer}])

[robert.eibers]

Hey @July ,

so you want something like:
avg(ifelse({Order Date}>=${pLastMonth} AND {Order Date}<=${pLastMonthEnd}, Sales,NULL))-avg(ifelse({Order Date}>=${pThisMonth} AND {Order Date}<=${pThisMonthEnd},Sales, NULL))
Basically you want the avg from sales for last month minus the avg from sales for this month.
The parameters are relative dates: the first one is start of last or this month and the end parameter is end of last or this month.

Hopefully I understood your Use-Case and could help you. If you have some more questions feel free to ask.

BR
Robert

[July]

Hi @robert.eibers

It’s close to working.

avg(ifelse({previous_month} = 1, {limit_p}, 0)) - avg(ifelse({this_month} = 1 , {limit_p}, 0))

My limit_p is => avgOver(avg({limit_p), [{id_customer}])

I need my calculated field to average the limit_p field

Tks!

[Kelseykl]

Hi July,
Not sure if this is the same as what you are trying to solve, but if I was trying to do:
Average over last month - Average over this month

This is what I might do depending on the visual/metric need:

1. Define month:

• MONTH= truncDate("MM",{Date})

2. Get the monthly average:

• THIS_MONTH_AVG = avgOver(avg({some_value}),[{partition_field},{MONTH}],POST_AGG_FILTER)

3. Look at the previous monthly average:

• LAST_MONTH_AVG = lag({THIS_MONTH_AVG },[{MONTH} ASC],1,[partition_field])

4. Take the difference:

• DIFFERENCE = {LAST_MONTH_AVG} - {THIS_MONTH_AVG }

Then if I wanted to display this in a table, and filter for only this month and last month I could do something like below (The values seen below are dummy data):

Hope this helps! Feel free to reach out if you have any questions.

[July]

hi @Kelseykl

I need the percentage difference. How can you make this “-31” value turn into a percentage?

Tks

[Kelseykl]

@July,

1. To calculate the Percent Change you can:

• Create another calculated field:

PERCENT_CHANGE = (({THIS_MONTH_AVG }-{LAST_MONTH_AVG})/{LAST_MONTH_AVG})

• Add as a value to the visual
• Select the ellipse next to the value, and change “Show as” to percentage (See the image below)

2. To calculate the Percent Difference you can:

• Create another calculated field:

PERCENT_DIFFERENCE = (({THIS_MONTH_AVG}-{LAST_MONTH_AVG})/(({LAST_MONTH_AVG}+{THIS_MONTH_AVG})/2))

• Add as a value to the visual
• Select the ellipse next to the value, and change “Show as” to percentage (See the image below)

3. If you want to remove the negative then you can adjust the calculated fields to use absolute value. For example:

PERCENT_DIFFERENCE = (abs({THIS_MONTH_AVG}-{LAST_MONTH_AVG})/(({LAST_MONTH_AVG}+{THIS_MONTH_AVG})/2))

Visual below contains dummy data

[July]

Hi @Kelseykl

I created these calculated fields but I get the “missing visual” error

The current month must be according to what the user chooses in the filter, and the previous month must be the month before this current month

How to do this?

N_mes_atual = truncDate(“MM”,{data_oferta})

N_media_mes_atual = avgOver(avg({limite_pa}),[{id_cliente},{n_mes_atual}],POST_AGG_FILTER)

N_media_mes_anterior = lag({N_media_mes_atual},[{n_mes_atual} ASC],1,[id_cliente])

N_media_mes_diff= {N_media_mes_anterior} - {N_media_mes_atual}

[Kelseykl]

Hi @July,

I believe this error is due to the monthly average calculated fields referencing the field “N_mes_atual”, but it is not included in the visual.

Here are two options that may fix this:

1. In the visual “Group By” fields, replace “data_oferta (MONTH)” with the field “N_mes_atual”

OR

2. Update the calculated fields to use "data_oferta " instead of “N_mes_atual”

N_media_mes_atual = avgOver(avg({limite_pa}),[{id_cliente},{data_oferta}],POST_AGG_FILTER)

N_media_mes_anterior = lag({N_media_mes_atual},[{data_oferta } ASC],1,[id_cliente])

[July]

Hi

I’m almost there

The “N_mes_atual” should be the current month (today).

I need to put in one KPI the general average of all customers in the current month (e.g. March), in another card the general average of the previous month (e.g. February)

After that, I will use the percentage difference between them.

[Kelseykl]

Hi @July,
To apply this to KPIs you may be able to do the following to get the result you are looking for:

1. This Month Average KPI visual

• Add a KPI visual
• Under “value” add the attribute you want to aggregate on, and set the “aggregate” to “Average”

Example

• Under “Trend Group” add the attribute “MONTH” or add the date attribute and set the “aggregate” to “Month”

Month calculated field

@Kelseykl

MONTH= truncDate("MM",{Date})

• Filter the visual for this month

This Month Filter

• If you need to apply any other partition attributes to the visual, then in this example you can apply them as additional filters

• Final Result should look something like this:
  
  Visual uses dummy data

2. Last Month Average KPI visual

• Add another KPI visual
• Repeat the steps from #1 with the following adjustment:
  • Set the date filter to the previous month

Previous Month Filter

• Final Result should look something like this:
  
  Visual uses dummy data

3. Percent Change KPI visual

• Add another KPI visual
• Repeat the steps from #1 with the following adjustments:

1. Set the date filter to include this month and the previous month

Month Filter

2. Go to the visual properties via the pencil icon in top-right corner of the visual

Visual Properties Icon

3. In the “Properties” pane on the right-hand side set:

• “KPI Options” → “Primary Value” → “Primary value displayed” = “Comparison”
• “KPI Options” → “Comparison” → “Comparison method” = “Difference as percent (%)”

Example

• Final Result should look something like this:
  
  Visual uses dummy data