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:
|Memory 32 GB||Memory||AT||Jan-18||140|
|Memory 32 GB||Memory||DK||Jan-18||1400|
|Memory 32 GB||Memory||AT||Feb-18||130|
|Memory 32 GB||Memory||DK||Feb-18||1300|
|Memory 32 GB||Memory||AT||Mar-18||125|
|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