topic Re: Build a supply curve in QlikView

Build a supply curve

Wed, 25 Nov 2020 16:16:04 GMT

Good day,

While doing my coursework, I've faced a problem with building an aggregate supply curve of different companies. I have several companies with their prices and volumes. I need to sum each volume to build a supply curve. Is there a way to do it in QlikView?

Here is an example of 2 companies, but it could much more of them. Also, I attached an Excel file to understand what supply curve is.

Re: Build a supply curve

MarcoWedel — Sat, 19 Mar 2016 00:59:30 GMT

Hi,

maybe one solution could be:

tabPriceVol:
LOAD 'A' as Company,
    Price,
    RangeSum(Volume,-Previous(Volume)) as Volume
INLINE [
    Price, Volume
    1, 5
    3, 9
    14, 10
    17, 23
    25, 33
    30, 33
    37, 39
];

LOAD 'B' as Company,
    Price,
    RangeSum(Volume,-Previous(Volume)) as Volume
INLINE [
    Price, Volume
    4, 5
    7, 9
    19, 9
    22, 23
    27, 23
    41, 44
    45, 55
];

LOAD 'Aggregated' as Company,
    Price,
    Volume
Resident tabPriceVol;

hope this helps

regards

Marco

Re: Build a supply curve

Sat, 19 Mar 2016 09:39:33 GMT

Hello Marco Wedel,

Thanks for your answer. But still, it is not what I am looking for. In my case, I am seeking to know if there is a way to automatically generate an aggregate supply table (last table in Excel file) or chart. As I mentioned before, I can manually calculate a curve for several companies but if there are 20-30 companies it becomes almost impossible.

Re: Build a supply curve

MarcoWedel — Sun, 20 Mar 2016 01:10:39 GMT

maybe it helps to present my chart as straight table and compare to your specified aggregated supply table:

regards

Marco

Re: Build a supply curve

Sun, 20 Mar 2016 15:50:59 GMT

MarcoWedel‌,

Sorry but I can not open the "QlikCommunity_Thread_210174_v2.qvw‌" file because of my Personal Edition. Also, I 've discovered a function Total in Dimension limits. It sums dimension values but only if all expressions have the same Dim values. Example:

When I use Total function in Dimension limits, it sums only distinct values:

So, if is there a way to assign a (n-1) value for each blank space? Example: if a blank space is and [i-1]=17.2, then =17.2.

Re: Build a supply curve

MarcoWedel — Sun, 20 Mar 2016 17:38:08 GMT

attached some screenshots that should help you to recreate my example using your personal edition:

hope this helps

regards

Marco

Re: Build a supply curve

Sun, 20 Mar 2016 18:27:21 GMT

MarcoWedel‌,

I can not use Accumulation either Horizontal.

Re: Build a supply curve

MarcoWedel — Sun, 20 Mar 2016 21:25:53 GMT

please post sample application to check.

thanks

regards

Marco

Re: Build a supply curve

swuehl — Sun, 20 Mar 2016 21:30:53 GMT

Change your chart from a pivot to a straight table.

Then check if you can reproduce Marco's results and if these match your requirement.

If they do match, I believe he can also solve your requirements how to display the curve as next step.

Re: Build a supply curve

Mon, 21 Mar 2016 06:39:02 GMT

swuehl‌‌, MarcoWedel‌, thanks for the advice. Now it works.

As swuehl has mentioned before, can you help me to display the curve?

Re: Build a supply curve

Mon, 21 Mar 2016 10:01:26 GMT

Also, I have a problem with data import. I import an input Excel file with multi header feature, transform it with multi_header_pivot_import.qvw script. When I try to create an aggregated table, it doesn't work for me. You can look at the samples below.

Re: Build a supply curve

MarcoWedel — Mon, 21 Mar 2016 11:41:24 GMT

try switching your chart type from "straight table" to "line chart".

Re: Build a supply curve

MarcoWedel — Mon, 21 Mar 2016 11:45:22 GMT

please open another thread for your multi header row excel import issue and close this one after achieving a line chart with your initial data.

thanks

regards

Marco

Re: Build a supply curve

Mon, 21 Mar 2016 12:15:38 GMT

MarcoWedel‌, I mean not the import issue, but a problem with straight table building. I've successfully uploaded a cross-header chart, but cannot build a table. Can you look at my Aggr.qvw sample?