Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Sample data set :
Distributor | FY 2018-2019 Value | FY 2017-2018 Value | FY 2018-2019 Value Contribution | FY 2017-2018 Value Contribution |
---|---|---|---|---|
a | 6,666.0 | 567.0 | 9.1% | 3.1% |
ABC | 4,563.0 | 5,432.0 | 6.2% | 29.8% |
c | 1,111.0 | 789.0 | 1.5% | 4.3% |
d | 2,222.0 | 987.0 | 3.0% | 5.4% |
f | 3,333.0 | 563.0 | 4.5% | 3.1% |
e | 55,555.0 | 9,871.0 | 75.6% | 54.2% |
variables :
v_FYear = if(getselectedcount(FiscalYear)=1,1,0)
v_Year=if(getselectedcount(Year)=1,1,0)
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
Hello All,
I'm trying to filter the dimension (Distributor) by using Alternate state or by using the directly using the "Distributor.name " in the if condition
1.METHOD 1 : using the directly the "Distributor.name " in the if condition
=if(Distributor.Name='ABC' and v_Year=1,Sum({$<Year={$(=max(Year)-1)}[Value])/100000 , if(Distributor.Name='ABC' and v_FYear=1,sum({$<[FiscalYear]={"$(=Only((SubField([FiscalYear],'-',1) - 1) & '-' & (SubField([FiscalYear],'-',2) - 1) ))"}> [Value] )/100000,'') |
Below is the screenshot of the output I get :
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
2. METHOD 2 : By using the Alternate State
Alternate_state1 is the list box for the Distributor names
Here is syntax that I used for alternate state method to get the previous year value in the expression
=if(v_Year=1,Sum({$<Year={$(=max(Year)-1)}>}{[Alternate_State1]*$}[Value])/100000,if(v_FYear=1,sum({$<[FiscalYear]={"$(=Only((SubField([FiscalYear],'-',1) - 1) & '-' & (SubField([FiscalYear],'-',2) - 1) ))"}>} {[Alternate_State1]*$}[Value] )/100000,''))
The above atlernate state method is used to output the previous calendar / fiscal year , but my output shows the Current year instead of Previous year .
------------------------------------------------------
Here is syntax that I used for alternate state method to get the previous year share of business in the expression
=if(v_Year=1 ,
(sum({$<Year={$(=max(Year)-1)}>}{[Alternate_State1]*$}[Value])/sum({$<Year={$(=max(Year)-1)}>}TOTAL[Value])),
if(v_FYear=1,
sum({$<[FiscalYear]={"$(=Only((SubField([FiscalYear],'-',1) - 1) & '-' & (SubField([FiscalYear],'-',2) - 1) ))"}>} {[Alternate_State1]*$}[Value] )
/sum({$<[FiscalYear]={"$(=Only((SubField([FiscalYear],'-',1) - 1) & '-' & (SubField([FiscalYear],'-',2) - 1) ))"}>} Total[Value] )))
- the above expression calculates current years value / previous year total value which is wrong as I need previous year divided by previous year total
-
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Thanks in Advance
Arun