Expressions to Show Change of Each Input

[#1]

Hello community,

I would like to create a waterfall chart that shows the change in a calculation between two days.  My calculation is as follows:

(a + b) * c / d / 100 = Desired Value

a,b, and c are summable across all records, but d is an exchange rate.  What I'd like to do is write expressions that show how the change of each variable for each record contributes to the overall change between the two days.  For instance

((a2 - a1) + b1) * c1 / d1 / 100 = effect of change in a

I would then create 3 more expressions doing the same thing for each variable in the calculation.

The lowest level of granularity is a combination of Account and LegID.  I'm pretty sure I'll need to use the AGGR function, but I'm having a hard time getting it to work.  Also, if I end up using the AGGR function, what will that do to cases where new Account/LegID combinations show up on day 2?

Any help is greatly appreciated!

[#2]

Here is some sample data.  I imagine I would do a waterfall chart using the bar offset property to show how each input affects the output.

[#3]

Hi Eric,

My idea is to find difference between desired value in day(n) and day(n-1).

This difference should be sum of a,b,c and d impact. Now i can imagine, that between stakes representings values for days (n-1) and (n) and some ID we can draw additional 4 stakes representing impact of a,b,c,d change. We have offset and and lenght (in my xls impact) of each.

There is still question what with new ID and discontinued ID. If you need waterfall also there,we need only find better factor function.

Let me know what do you think about this way of thinking?

If you like it, we may improve it and then i will try to recommend how to set up it in QV.

regards

Darek

[#4]

Hi Dariusz,

Thank you for your insight.  I like the direction that is going.  I thought about the same thing regarding the change in the population.  This could add two new expressions in QlikView.  One for New IDs and another for Dropped IDs.  I've attached a sample of what the chart might look like.  Look at the worksheet called "Mockup Chart".  I also changed one of the Exchange Rates (d) because it was causing a wild swing.

[#5]

Eric,

How many days will be presented on your chart at one time?

For me waterfall in your case looks too wide and it is difficult to find which component impacted down, which up.

I think, good idea may be to use colors. For example red for a, blue for b, green for c, brown for d etc.

We may draw one "change impact stake" for each day. If we further will use some symbol for Desired Value (for example cross), we may have cross on our stack, so it will be extremally thin ....

It remains a matter of presentation, which component change affects the growth and a decrease in the Desired Value.

I have few ideas here:

1. Stacks under cross shows componets which decrease value, stacks over cross - increase it.

2. We use double colors, for example light red - a change decreases value, dark red - a change increases value, etc.

3. We use two stacks - one with components, which decreases, 2'nd with components, which increases. In that case we may try to build waterfall from those stacks.

Of course last word belongs into recipient of analysis

I'am also thinking about "impact factor forula"

Don't you think, that:

i(a) = f(a2,b1,c1,d1)-f(a1,b1,c1,d1)

i(b) = f(a1,b2,c1,d1)-f(a1,b1,c1,d1)

i(c)= f(a1,b1,c2,d1)-f(a1,b1,c1,d1)

i(d) = f(a1,b1,c1,d2)-f(a1,b1,c1,d1)

may be better?

Or maybe something like this:

i(a) = f(a2,b1,c1,d1)/f(a1,b1,c1,d1) ...

where f(a,b,c,d) is your Desired Value formuala; a2 is a value for next day, a1 is a value for previous day.

Waiting for your answer

regards

Darek

P.S. I think, it will be not very difficult to set up it in QV, but first we must be sure, that we have good idea

[#6]

Hi Dariusz,

I want to give the user the ability to compare only two days at a time. Otherwise I agree that this would be too many bars. Also, this would be a dimensionless chart, so I’m envisioning 8 expressions:

Prior Day Value

Effect of Dropped IDs

Effect of New IDs

Effect of Change in a through d

Next Day Value

If the user selects more than one day, our set expressions can have something like {<[Business Date]={"$(=Date(Max(),'M/D/YYYY'))"}>} and the same thing for min.

i(a) = f(a2,b1,c1,d1) – f(a1,b1,c1,d1) etc. works great. Another way to express it would be i(a) = f(a2-a1,b1,c1,d1). I don’t know which way would be easier to set up or more efficient for QlikView to calculate.

i(a) = f(a2,b1,c1,d1)/f(a1,b1,c1,d1) is an interesting idea. It expresses the day 2 as a % of the base, am I right? What about i(a) = f(a2-a1,b1,c1,d1)/ f(a1,b1,c1,d1)? This would express the CHANGE as a percent of the base.

That being said, I also like your idea of viewing the contribution of change along a dimension, in which case I would agree with you that a waterfall doesn’t make sense. After we get the above view going, I think it would be easy for me to use the same logic to apply the changes to dimensions.

[#7]

Defining impact functions as: i(a) = f(a2,b1,c1,d1) – f(a1,b1,c1,d1) or  i(a) = f(a2,b1,c1,d1) / f(a1,b1,c1,d1) looks for me better, especially when we tell about (not sumable) d.

f(a2-a1,b1,c1,d1) <> f(a2,b1,c1,d1) – f(a1,b1,c1,d1)

Do you assume that d2 always (as business day) immediately follows d1?

What data volumes do you expect in your application?

regards

Darek

[#8]

I agree with you about the formula approach. We can’t assume that d2 is the day after d1. For one thing they are business days only, additionally I would expect the user to select any two dates to compare. The app will hold around 300k records by about 30 columns wide, not very much data.

[#9]

Hi,

i prepared first version of your chart.

I think, there must be also some simplier way, but today i have no more power to fight

You will find that chart is build for min and max dates. So, if u select only one date, you will have not difference.

There is calculation condion on the chart, so calculation is not made if there is only one possible date.

Of course you may make selections in Identifiers.

For new and dropped ids i took thems desired value as impact, but for new in + and for dropped in -.

For a,b,c,d impact i took respectively f(a2,b1,c1,d1)-f(a1,b1,c1,d1);f(a1,b2,c1,d1)-f(a1,b1,c1,d1) ;f(a1,b1,c2,d1)-f(a1,b1,c1,d1);f(a1,b1,c1,d2)-f(a1,b1,c1,d1).

For for a,b,c and d impact I standarized  distance between f(d1)+f(new)-f(dropped) and f(d2).

I tryed also one more generic approach, with your chart dimension points as island and only one expression for lenght and one for offset, but i had trouble with dimension values...

I'am not sure what about perormance. There is a lot of Set Analysis.

Anyway, I hope it helps you

regards

Darek

[#10]

Hi Dariusz,

A million thanks! I’ll need some time to digest what you’ve done here, but it really is amazing!