
Re: Expressions to Show Change of Each Input
Eric Poole Mar 27, 2014 1:04 AM (in response to Eric Poole)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.

Sample Data.xlsx 11.5 K

Re: Re: Expressions to Show Change of Each Input
Dariusz Mielczarek Mar 27, 2014 5:38 AM (in response to Eric Poole)Hi Eric,
My idea is to find difference between desired value in day(n) and day(n1).
This difference should be sum of a,b,c and d impact. Now i can imagine, that between stakes representings values for days (n1) 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

Sample Data.xlsx 17.7 K

Re: Re: Re: Expressions to Show Change of Each Input
Eric Poole Mar 27, 2014 9:26 AM (in response to Dariusz Mielczarek)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.

Sample Data.xlsx 21.5 K

Re: Re: Re: Expressions to Show Change of Each Input
Dariusz Mielczarek Mar 27, 2014 11:35 AM (in response to Eric Poole)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

Re: Expressions to Show Change of Each Input
Eric Poole Mar 27, 2014 11:53 AM (in response to Dariusz Mielczarek)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(a2a1,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(a2a1,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.

Re: Expressions to Show Change of Each Input
Dariusz Mielczarek Mar 27, 2014 12:32 PM (in response to Eric Poole)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(a2a1,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

Re: Expressions to Show Change of Each Input
Eric Poole Mar 27, 2014 12:57 PM (in response to Dariusz Mielczarek)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.

Re: Re: Expressions to Show Change of Each Input
Dariusz Mielczarek Mar 27, 2014 9:45 PM (in response to Eric Poole)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

Sample Data.xlsx 12.4 K

impact_abcd_v1.qvw 158.8 K

Re: Expressions to Show Change of Each Input
Eric Poole Mar 28, 2014 10:20 AM (in response to Dariusz Mielczarek)Hi Dariusz,
A million thanks! I’ll need some time to digest what you’ve done here, but it really is amazing!

Re: Expressions to Show Change of Each Input
Dariusz Mielczarek Mar 28, 2014 11:20 AM (in response to Eric Poole)I like less conventional needs. It is always some challenge
You should test results of course! For me values looks nice, but of course it is important that user should know, what this "Change" on your chart mean. Anyway i think, this chart gives good view which component has more and which less impact on value change.
Let me know what do you think after deeper insight, and how it works with more data loaded.
Of course if you will have some questions, i will try to explain.
regards
Darek








