Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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:

  

PNFamilyCountryUploadCost
CPU1CPUATJan-18100
CPU2CPUATJan-18120
Memory 32 GBMemoryATJan-18140
CPU1CPUDKJan-181000
CPU2CPUDKJan-181200
Memory 32 GBMemoryDKJan-181400
CPU1CPUATFeb-1890
CPU2CPUATFeb-18110
Memory 32 GBMemoryATFeb-18130
CPU1CPUDKFeb-18900
CPU2CPUDKFeb-181100
Memory 32 GBMemoryDKFeb-181300
CPU1CPUATMar-1880
CPU2CPUATMar-18110
Memory 32 GBMemoryATMar-18125
CPU1CPUDKMar-18850
CPU2CPUDKMar-181215
Memory 32 GBMemoryDKMar-181315

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

1 Solution

Accepted Solutions
sunny_talwar

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

View solution in original post

8 Replies
sunny_talwar

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

Anonymous
Not applicable
Author

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
Not applicable
Author

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
Not applicable
Author

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.

2018-02-01 10_34_42-Qlik Sense Desktop.png

sunny_talwar

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
Not applicable
Author

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.