8 Replies Latest reply: Feb 2, 2018 4:00 AM by Jose Miguel Coelho

# Calculate MoM variation

Hello,

I'm fairly new at using Qlik Sense, albeit on the past weeks I've been using quite a lot to change my tools from excel. In all the cases I've been able to solve my problems with the help of this community, except for the one I'm posting today.

This is a simplified sample of the data, in the real case I have more than a million lines and many more columns:

 PN Family Country Upload Cost CPU1 CPU AT Jan-18 100 CPU2 CPU AT Jan-18 120 Memory 32 GB Memory AT Jan-18 140 CPU1 CPU DK Jan-18 1000 CPU2 CPU DK Jan-18 1200 Memory 32 GB Memory DK Jan-18 1400 CPU1 CPU AT Feb-18 90 CPU2 CPU AT Feb-18 110 Memory 32 GB Memory AT Feb-18 130 CPU1 CPU DK Feb-18 900 CPU2 CPU DK Feb-18 1100 Memory 32 GB Memory DK Feb-18 1300 CPU1 CPU AT Mar-18 80 CPU2 CPU AT Mar-18 110 Memory 32 GB Memory AT Mar-18 125 CPU1 CPU DK Mar-18 850 CPU2 CPU DK Mar-18 1215 Memory 32 GB Memory DK Mar-18 1315

PN- Part Numbers, how the product is identified

Family- Group of Part Numbers make a family. E.g If we have 3 different CPUs (Intel, AMD, QUALCOMM) they still belong to the same family, CPU.

Country- The country that has the cost using their local currency. AT, Austria (Eur) and DK, Denmark (DKK).

Cost- Cost of that product

What I'm trying to do is to create a chart based on the families month to month (MoM) variation, basically compare the cost of the PN in one month vs the previous month.

The chart should consider the MoM variance per family, but to do that needs to take into consideration the MoM average variance between all different Part Numbers from the same family and also the country. Consider all this is what is creating huge issues for me.

In a mathematical way would be:

MoM(Feb 2018)= (("Cost for AT Feb-18 for CPU1"/"Cost for AT Jan-18 for CPU1"-1)+("Cost for AT Feb-18 for CPU2"/"Cost for AT Jan-18 for CPU2"-1))/2

Mar(2018)= (("Cost for AT Mar-18 for CPU1"/"Cost Feb-18 for CPU1"-1)+("Cost for AT Mar-18 for CPU2"/"Cost for AT Feb-18 for CPU2"-1))/2

and keep doing that for each new month, country and with a much bigger range of Part Numbers.

I'm trying to add in a line chart or combo chart. Normally, I start by considering Upload and Family as dimension and cost as measure.

I already tried to use aggregates, average by distinct, aboves, sums, counts, but the formulas I've tried till now are not working or giving me the wrong results, whether I'm not considering the countries, or the above formula can't be applied on the way my sample is organized etc. .

Do you know how can I solve this case?

Probably I'm missing something very simple, but can't see what.

• ###### Re: Calculate MoM variation

The best way to approach this issue would be to use The As-Of Table

• ###### Re: Calculate MoM variation

Hello,

Thanks for your reply. But I'm afraid my problem remains. So I created the master calendar as the article mention. Now I'm able to have Upload, AsOfMonth, MonthDiff and YearDiff, but I still can't move much from here.

The first part is because the costs are not cumulative like sales, so I can't have them accumulating over time. Nevertheless, I tried an average to compare one month against previous month, so a MonthDiff=1, Avg({\$<MonthDiff={1}>} Cost).

But the result seems wrong, for example for CPU1 in AT:

• Jan- is the first month of my date, being equal to blank (It's fine here, the trouble starts for next months),
• Feb- It brings the cost of January 100
• Mar- brings the value from February, 90

Although for simple averages the results should be:

• Jan=Blank
• Feb-(Feb=Jan)/2=95
• Mar- (Mar+Feb)/2=85

I also tried the rolling averages without success and many other formulas. It seems the As-Of Table is very goodto do accumulations over time, but is not helping me creating the MoM Variance.

Am I doing something wrong here?

• ###### Re: Calculate MoM variation

Would you be able to share a qvf sample to check this out?

• ###### Re: Calculate MoM variation

Sure sending attachment, I tried different approaches today and i think i was able to make it work through:

Avg({\$<MonthDiff={1}>} Cost)/Avg({\$<MonthDiff={0}>} Cost)-1

This should be fine for the caluclations right?

If it is fine, I'm trying to go to the next step. Do the calculations for each PNs and just get the average of all of them (e.g (MoM CPU1 + MoM CPU2)/Count(distinct PN)), should I try the same approach as for months?

• ###### Re: Calculate MoM variation

Avg({\$<MonthDiff={1}>} Cost)/Avg({\$<MonthDiff={0}>} Cost)-1

This should be fine for the caluclations right?

Looks good to me

May be this

Avg(Aggr(Avg({\$<MonthDiff={1}>} Cost)/Avg({\$<MonthDiff={0}>} Cost)-1, PN))

• ###### Re: Calculate MoM variation

That was one of the first formulas I tried too, but for some reason it just shows me a dot and not a line

Normally, the line even starts on February, but with Aggr() starts in January for some reason, I tried multiple variations, but it seems i can't make the aggr() works with the line chart.

• ###### Re: Calculate MoM variation

Avg(Aggr(Avg({\$<MonthDiff={1}>} Cost)/Avg({\$<MonthDiff={0}>} Cost)-1, PN, AsOfMonth))

or

Avg(Aggr(Avg({\$<MonthDiff={1}>} Cost)/Avg({\$<MonthDiff={0}>} Cost)-1, PN, AsOfMonth, Country))

• ###### Re: Calculate MoM variation

Hi, thanks a lot for all the help.

it helped a lot and gave me the chance to learn about The As-Of Table and aggregations.

I will try now to study aggregations into more detail, they seem very useful.