topic Calculate link raletive ratio?? in QlikView

Calculate link raletive ratio??

Fri, 18 Feb 2011 03:38:29 GMT

Dear all,

I have a table named test_tb with structure below: ID, Supplier, Trade_name, Price, Date(8 string format such as 20080104,not in data format)

I want to calculate link relative ratio monthly. such as sum(price) of Oct is $100 , and Nov is $80, then the result will be (80-100)/100=-20%

I use expression Sum ({$<year={$(max_year)},month={11}>}price)-Sum ({$<year={$(max_year)},month={10}>}priceSum ({$<year={$(max_year)},month={10}>}price)) for 1 month.

Now I want to calculate 12months' link relative ratio? How can I do this? It's difficult to get Jan's data,because it will campare with Dec. of last year.

Calculate link raletive ratio??

tresB — Fri, 18 Feb 2011 05:53:41 GMT

i would suggest to use MonthName() function. Use two variables like

CurrentMonth=MonthName(YourMaxDate)

PrevMonth=MonthName(YourMaxDate, -1)

This would calculate the previous year's Jan automatically (with -1 given). Hope this helps you.

Regards, tresesco

Calculate link raletive ratio??

Fri, 18 Feb 2011 06:59:58 GMT

hi tresesco

The parameter YourMaxDate must be date format. But in my table, year and month are different fields((in number format)).

my table structure :

ID Year Month

001 2010 01

002 2010 02...

And I want to use field Month as the chart's dimension. When the Month=01(year2010), the previous month should be Month=12(year2009). How can I do this?

Thank you .

Calculate link raletive ratio??

tresB — Fri, 18 Feb 2011 07:09:01 GMT

make a load like this:

LOAD ID, Year, Month,

MakeDate(Year,Month,01) as Date FROM .....;

Then you can proceed as told earlier. It's always suggested to make a proper Date field availavle in your loaded data, because it makes your life easier dealing with many related operations.

Please let me know if you differ or so

Regards, tresesco.

Calculate link raletive ratio??

Sun, 20 Feb 2011 01:53:51 GMT

hi tresesco

Thanks for your reply. As your method, I insert a new field in my table. Like this:

ID Sales Year Month Date(new field with date format)

I set the calculated dimension in =MonthName(Date) , and I test the expression =sum(Sales).

But I'm confused how to write the link raletive expression, like =((Sum (current year, current month)-Sum (prevous year, same as current year))/Sum (prevous year, same as current year)

Calculate link raletive ratio??

tresB — Mon, 21 Feb 2011 06:19:40 GMT

Hi, There are other options you could solve your problem with. but i found it better to do it at the backend. Please find the attached application.

hope this helps you.

Regards, tresesco

Calculate link raletive ratio??

Mon, 21 Feb 2011 09:19:12 GMT

hi,

Thanks for your example of qvw file.

But my problem is a little more complicated.

The records are classify in to seval classes such as:

BaseData:
Load * Inline [
Date, Amount, ClassA, ClassB
01-03-2011, 1000,Animal,Horse
01-01-2010, 100,Plant, Flower
01-02-2010, 200,Plant, Grass
01-03-2010, 300,Plant, Flower
01-04-2010, 400,Animal,Tiger
01-05-2010, 5000,Animal,Horse
01-06-2010, 100,Plant, Flower
01-07-2010, 200,Plant, Grass
01-08-2010, 300,Animal,Tiger
01-09-2010, 4000,Plant, Flower
01-10-2010, 500,Plant, Grass
01-11-2010, 100,Animal,Horse
01-12-2010, 200,Plant, Flower
01-01-2011, 3000,Animal,Horse
01-02-2011, 400,Plant, Flower
];

User can choose which class depands on demand. Instead of calculating link raletive when ETL, I want to calculate link raletive by user's choice in a flexible way.

Calculate link raletive ratio??

tresB — Mon, 21 Feb 2011 09:45:44 GMT

Possibly this(attachment)?

Note: all months are required to be there in your data and the sort order matters.

Regards, tresesco

Calculate link raletive ratio??

Mon, 21 Feb 2011 10:01:46 GMT

I have know this method. But I chould not promise that there is data every month.

I will post details of my question hours later.

Calculate link raletive ratio??

Tue, 22 Feb 2011 01:07:12 GMT

hi, I edit your sample qvw, pls check it.

Thank you for being so patient .