Categories
i want to calculate amount in current Fiscal Year...
i am trying to get amount in current fiscal year 1-july-21 to 30th-june-22
count(case when "W_TIME_DIM"."FISCAL_YEAR" = '2021-22' then "C_INVOICE_DETAIL"."INVOICE_NO" end)
it is my formula but now i dont want to specify year every time. what can i do i am using OBIEE 12c
Answers
-
in my case what i am trying to do is to make automatically change current fiscal year when new year starts. currently i have to change the fiscal year every year
0 -
Set a repository variable containing your fiscal year, and then populate the variable with the correct logic for the need ("your" fiscal year follows your own rules, you should have a business logic defining how the fiscal year is defined, so implement that in a piece of SQL).
In your formula use the variable and done.
0 -
i am new to OBIEE can you please give any link about how to make variable
0 -
Additional to Gianni or better before going to Gianni's point you need to have an actual, properly formed time hierarchy to begin with:
0 -
select to_char(SYSDATE - NUMTOYMINTERVAL(1, 'year'),'yyyy')||'-'|| substr(to_char(sysdate,'yyyy'),3,2) from dual;
0 -
Whatever you do or choose to do - do NOT do that. It's code, it's not deterministic in terms of being modeled properly and it is beyond non-performing because while it may "work" for a single row this will NOT scale for multi million or even billion row facts which is what normally gets analyzed in Analytics.
0
