Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

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

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.

1 Solution

Accepted Solutions

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

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]

talk is cheap, supply exceeds demand
5 Replies

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

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


talk is cheap, supply exceeds demand
Not applicable

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

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.

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

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]

talk is cheap, supply exceeds demand
Not applicable

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

Thank you! I will give it a try.

Not applicable

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

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

Community Browser