Showing all value in selection box

[swiftfahad]

hi all,

I have a dimension  of fiscal year in my app which I have given to the user through selection box.

data: 

head	cc	fyear	amt
12abc	xyz	2001-2002	500
12abc	xyz	2019-2020	1000
22fxt	reee	2005-2006	200

now I want to allow user to see the total amount allocated so far till selected fiscal year.

suppose user selects 2022-2023 from selection box so the table /text box show the sum(amt)  till starts of 2022-2023 using my expression.  (its done)

My problem is when user make selection of head:12abc and cc=xyz through selection box, my fiscal year selection box limits to 2001-2002 and 2019-2020 only.

How can I allow/show user to select any of the value among all the value of fiscal year my data have i.e. from 2000-2001  to  2022-2023.

should I crated dummy fiscal year field contain all fiscal year data or what?

[swiftfahad]

No one interested? Is this such hard? How can i provide user a selection of current fiscal year? 

[Taoufiq_Zarra]

@swiftfahad  can you share a sample data and the expected output ?

[swiftfahad]

see attached file

[swiftfahad]

let me simplify my problem a bit more...

my management want to see the total amount till today/current fiscal year (2022-2023)

now how can I made available the selection of 2022-2023.? 

[vinieme12]

Associate Fyear to all possible head and cc combinations in a separate dimension table as below

Then use the Dimension filters of fyear from the Fyear_Dim table instead

++UPDATE++

Load num(year(today())-recno())&'-'&num(year(today())-recno()+1) as fyear

Main:
Load *, head&'-'&cc&'-'&fyear as fyear_key inline [
head,cc,fyear,amt
12abc,xyz,2001-2002,500
12abc,xyz,2019-2020,1000
22fxt,reee,2005-2006,200
22fxt,reee,2005-2006,200
22fxt,reee,2005-2006,200
22fxt,reee,2005-2006,200
33erd,reee,2005-2006,500
33erd,reee,2009-2010,300
33erd,reee,2020-2021,1500
12abc,xyz,2022-2023,200
];

temp_FYear:
Load Distinct head,cc
Resident Main;
Left Join(temp_FYear)
Load num(year(today())-recno())&'-'&num(year(today())-recno()+1) as fyear
Autogenerate((year(today())+1)-2001);

NoConcatenate
Fyear_Dim:
Load *, head&'-'&cc&'-'&fyear as fyear_key
Resident temp_FYear;
drop table temp_FYear;
drop field head,cc,fyear From Main;

exit script;