Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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 .
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.
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)
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
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.
Possibly this(attachment)?
Note: all months are required to be there in your data and the sort order matters.
Regards, tresesco
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.
hi, I edit your sample qvw, pls check it.
Thank you for being so patient .