How to calculate volume and mix effect last year vs current YTD

[#1]

Hello everyone!

I'm completely new to Qlik and have downloaded Qlik Sense Desktop to try out if the product to see if it can help sorting out some analysis need.

My first findings is very promising but I get stuck when trying to create a simple Volume and Mix effect example. Now, my customer do a lot of this type of anlysis and I really want to be able to show an example of this in my prototype.

In the attached excel file I have two sections

1.  the volume and mix calculations I want to be able to do

2. simple example of how the input file might look like. Most important aspect of the input is the date which is on a day level. Now the analysis should be for full months.

Interesting comparison alternatives (for both cost and cost/unit):

can be last year YTD vs this year YTD.

Last year monthly average vs this year monthly average.

If anyone can guide me in a pedagogical way on how to achieve this, maybe with some example scripting and epressions, I will be most grateful.

[Gysbert_Wassenaar]

I think you'll find this blog post of interest: The As-Of Table. This applies to both Qlikview and Qlik Sense btw.

[#3]

Yes it was interesting for sure and can be useful. But it was not enough for me to understand how to solve my original question.

[Gysbert_Wassenaar · Accepted solution]

You have a date field. You create an AsOf table based on that date field like explained in that blog post. Then your create a set of set analysis expressions just like in the blog post for YTD and previous YTD for units and cost.

• YTD Units: sum({<YearDiff={0}>}Units)
• YTD Cost: sum({<YearDiff={0}>}Cost)
• PYTD Units: sum({<YearDiff={1}>}Units)
• PYTD Cost: sum({<YearDiff={1}>}Cost)
• YTD Cost/Unit: [YTD Cost]/[YTD Units]
• PYTD Cost/Unit: [PYTD Cost]/[PYTD Units]

[#5]

Thank you! I will give it a try.

[#6]

I've now had some time to test an got it tio work using As-of-Table and Set Analysis expressions. To compare YTD for a selected month vs YTD previous year for the same month I used this expression:

$<MonthDiff={">11"},YearDiff={1}>