how to implement Accouting Quarter Numbers based on Period Names

Question

Below filter is written based on Period Number as Input prompt, but now I need to change this Period Number to Period Name, tried several ways of changing it but could not achieve.

"Fiscal Calendar"."Accounting Quarter" = case when @{PERIOD}{5} in (1,2,3) then 1 when @{PERIOD}{5} in (4,5,6) then 2 when @{PERIOD}{5} in (7,8,9) then 3 when @{PERIOD}{5} in (10,12,11) then 4 end

Expecting something like below or something which accepts Period name as input and gives Accounting Quarter as output

"Fiscal Calendar"."Accounting Quarter" = case when @SUBSTRING({PERIOD} FROM 1 FOR 3){Jul} in (’Feb’,’Mar’,’Apr’) then 1 when @SUBSTRING({PERIOD} FROM 1 FOR 3){Jul} in(’May’,’Jun’,’Jul’) then 2 when @SUBSTRING({PERIOD} FROM 1 FOR 3){Jul}  in (‘Aug’,’Sep’,’Oct’) then 3 when @SUBSTRING({PERIOD} FROM 1 FOR 3){Jul}  in (‘Nov’,’Dec’,’Jan’) then 4 end

Viswanath-Oracle · Answer

This can be resolved by simply creating a new hidden prompt Period Num in dashboard which will take input as Period Name from main dashboard prompts

next in other section place the report which will automatically pick up period Num from above hidden prompt.

in this case no changes to existing report is needed, report was wrking perfect.

Joshua C. Stewart · Answer

Are you getting any error message on the calculation? Do you have any of the session logs to see what this computes to?