Data chart view by different currency and fiscal year

[#1]

Hello Guys

I'm building a chart to analysis data, meanwhile users can switch to display data between fiscal year with calendar year and among different currency.

However, there's something wrong with the script. I have uploaded all of the files.

The 17A JOB list.xlsx (job sheet) contains the real data, the Rate.xlsx contains the currency rate.

In QVW file, I converted the number to date format because of the date type column loading issue:

Date(Num#(Month),'YYYY-MM') as TestedDate Resident Table1;

Then I defined fiscal year:

if(month(TestedDate)>=4,

if(month(TestedDate)>=10,right(year(TestedDate),2)&'B',right(year(TestedDate),2)&'A'),right(year(TestedDate)-1,2)&'B') as FY

But when I combined these two functions in one script, reloading fails all the time.

I checked the script, seems the fiscal year definition statement need to modify, but I have no idea which place is the right one to insert into.

Someone please help to give me some advice, thank you...

[rahulpawarb]

You have placed definition of FY field at wrong place (after loading Table2); Add the same in preceding load of Table2 (this will give you two fields in Table2 - TestedDate & FY).

Also observe that in present data model all tables are island tables; no relation between any two tables. Strange!

Regards!

Rahul Pawar

[#3]

Hi Rahul

But the TestedDate is defined after loading Table2, if I put FY defining statement before loading Table2, it is not able to find the real date. And the TestedDate is coming from Date(Num#(Month),'YYYY-MM') as TestedDate Resident Table1; this statement.

So the order I supposed is first change the Date format from number to calendar date, then define the fiscal year, then add currency switch function.