Expressing Week2Week Change in Diagram

[#1]

Hi Community,

my problem: i get new numbers für revenue every week. The numbers are divided up into different regions.

So at the moment i sum them and show them in a bar chart by first dimension date. This works really good.

Now i want to have a second chart which shows not the actual revenue but the week to week change. The math behind the problem is really easy: NewTotalRevenue - OldTotalRevenue = w2wChange.

But how can i tell QlikView to do it in a diagram?

Please help!

Thank you! 🙂

Markus

[#2]

oh very interesting i found a way to do it with the above function in the diagram formula. I think this works. Is it a good way or should i use a table / script ?

[#3 · Accepted solution]

Hi Markus,

please see the attached example for other ideas (Set Analysis).

It´s always worth to have this in mind.

Have a successful week!

Rainer

[#4]

Hi thank you very much for this file. I have to get some more information about the date functions to really understand it in detail but in general i see your way.

Maybe you can help me with another question:

My solution with the above function makes some trouble:

Everything works fine when a have not a special selection. But in fact i have different regions. So if i want to see not all regions but only one or two which i choose by selection then the diagram shows crazy numbers... i dont know why i think its because of the above function. All diagrams with this function become wrong all other ones are still correct.

SUM ( {$ < Brand = {"A"} >}[Revenue]) - above (SUM ( {$ < Brand = {"A"}>}[Revenue]))

[#5]

Hi Markus,

are you able to share an example (*.qvw)?

Not sure what do you mean by "crazy numbers" or "become wrong".

Rainer

[#6]

Rainer, thank you very much for your offering to help!

To post a qvw is difficult for me. Data are confdential and it is not that easy to create a dummy version. But maybe you can help me to create a formula based on your solution:

So this is yours:

Sum

( {< Datum={">=$(=addmonths(monthstart(max(Bestelldatum)),-1)) <=$(=addmonths(monthend(max(Datum)),0))"}>} UmsatzBestZeile)

You want to sum up "UmsatzBestZeile". I think the expression between the first { and the last } describes the timeframe.

so: starting point:

={">=$(=addmonths(monthstart(max(Bestelldatum)),-2))

ending point:

<=$(=addmonths(monthend(max(Datum)),0))"}

You start on the first day of two months ago. You end on the last day of current month.

I want to sum up [VLC Actual]. i have a date (yyyy-mm-dd) field [Date_long] which is also the first dimension of the my chart. On each day week (7day period) I want to show [VLC Actual] from this week - (minus) [VLC Actual] from the week before. (to see the difference)

So i think my formula should look like this:

Sum

( {< Date_long={">=$(=week(Date_long)-1)) <=$(=week(Date_long))"}>} [VLC Actual])

But it does not work. I think i have some mistake in my syntax.

Maybe you can help me with that? Thanks!

[#7]

Markus,

you are comparing a Date format (DD.MM.YYYY) with a Week (13). That´s one reason your expression didn´t work.

See my modified example. Now you are able to do calculations on a week basis.

Hope it gives you some more ideas.

Rainer

[#8]

Thank you Rainer, großes Dankeschön.

I adopted the table into my qlikView document. It works. Now i have to produce a barchart with date as first dimension. It should show the growth from week 2 week for example for a whole Quarter or year. Not only the growth of one selected date in comparison to the date before. I will first try to do it in your sheet.