Calculate link raletive ratio??

[#1]

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.

[tresesco]

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

[#3]

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 .

[tresesco]

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.

[#5]

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)

[tresesco]

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

[#7]

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.

[tresesco]

Possibly this(attachment)?

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

Regards, tresesco

[#9]

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.

[#10]

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

Thank you for being so patient .