Hi everybody,
I have an issue , this is the data that i want to have but is base on a expression with static variable,
and if i put dynamic variable in a bar char it give me the Q 2 19 quarter values (first row) , i need all the quarter (o put in my formula :
count ( DISTINCT
total {1<C_Quarter=, flag_date={0}, C Fiscal Quarter number={">=$(v Dynamic Quarter 4) <=$(v Dynamic Quarter)"},
C Q Fiscal Quarter number={"<=$(v Dynamic Quarter) >=9999"}>} NUMBER)
/
count
(total DISTINCT
{1< C_Quarter=, C Fiscal Quarter number={">=$(v Dynamic Quarter 4) <=$(v Dynamic Quarter)"},
c Q Fiscal Quarter number={"<=$(v Dynamic Quarter) >=9999"}>} NUMBER)
Where
v Dynamic Quarter=max(C Fiscal Quarter number)
v Dynamic Quarter 4=max(C Fiscal Quarter number)-4
and 9999 are null values;
| C_Quarter | total | respond | column(2)
/
column(1) |
| Q 2 19 | 127283 | 57272 | 45.0% |
| Q 1 19 | 98166 | 42358 | 43.1% |
| Q 4 18 | 67914 | 24649 | 36.3% |
| Q 3 18 | 38248 | 12300 | 32.2% |
| Q 2 18 | 14896 | 1158 | 7.8% |
| | | | |
| | | | |
One solution is that i use pick(match(quarter,'Q 2 19','Q 1 19'...),
and add the expression from above with static variable for every Q and it works
But searching the web i found :
range sum(
count(distinct {<flag_date={0}, C Fiscal Quarter number ={">=$(=$(d Quarter 4)) <=$(=$(d Selected Quarter))"},cQ Fiscal_Quarter number ={"<=$(=$( d Selected Quarter)) >=9999"}>} NUMBER)
,below(
range sum(
count( distinct {<flag_date={0}, C Fiscal Quarter number ={">=$(=$(d Quarter 4)) <=$(=$(d Selected Quarter))"},c Q Fiscal Quarter number ={"<=$(=$(d Selected Quarter)) >=9999"}>} NUMBER))
)
)
where d Selected Quarter and d Quarter 4 are dynamic variable =max(C Fiscal Quarter number ) , max(C Fiscal Quarter number )-4 (without 😃 created in "front".
but i doesn't give me
I want to use a count(NUMBER) where C Fiscal Quarter number is between current quarter and four previous quarters, with a flag_date =0(not important) and
the second filter is c Q Fiscal_Quarter number <= current quarter >=9999 (null values) . c Q_Fiscal_Quarter number and from a second calendar .
I have added am excel with the demo data. For a better selection you have to select the current quarter and all the previous quarter for C Fiscal Quarter number and from c Q Fiscal Quarter number the same selection + 9999 and flag =0
thank you.
Adrian
