Calculate MoM variation

[Anonymous]

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).

Upload- The month and year I uploaded the cost from SQL.

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.

Thanks for your help

[sunny_talwar]

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

[Anonymous]

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?

[sunny_talwar]

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

[Anonymous]

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?

[sunny_talwar]

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))

[Anonymous]

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.

[sunny_talwar · Accepted solution]

How about this

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))

[Anonymous]

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.