Set Analysis Problem (Year / Year-1)

[#1]

Hi,

I need some help with Set Analysis.

Here is the context :

I have a table with these expressions:

- Quantity for the selected month --> No problem

- Quantity for the current fiscal year till the selected month --> No Problem

Sum({<Fiscal_Year= {$(=Max(Fiscal_Year))}, Mois_Exercice = {"<=$(=Max(Fiscal_Month))"} >}Quantity)

- Quantity for the previous fiscal year till the selected month. --> Problem

Sum({<Fiscal_Year= {$(=(Max(Fiscal_Year) -1))}, Mois_Exercice = {"<=$(=Max(Fiscal_Month))"} >}Quantity)

For example :

(What I want)

Civil Month selected : August (correspond to Fiscal Month 10) Fiscal Year selected : 2009-2010

Quantity( Fiscal Year = 2009-2010 Fiscal Month : 1 to 10 ) 50

QuantityPrevious Fiscal Year = 2008-2009 Fiscal Month : 1 to 10 ) 60

--------------------------------

(What I have)

Civil Month selected : August (correspond to Fiscal Month 10) Fiscal Year selected : 2009-2010

Quantity( Fiscal Year = 2009-2010 Fiscal Month : 1 to 10 ) 50

QuantityPrevious Fiscal Year = 2008-2009 Fiscal Month : 1 to 10 ) 0

--------------------------------

(What I have bis)

Civil Month selected : August (correspond to Fiscal Month 10) Fiscal Year selected : 2009-2010 and 2008-2009

Quantity( Fiscal Year = 2009-2010 Fiscal Month : 1 to 10 ) 50

QuantityPrevious Fiscal Year = 2008-2009 Fiscal Month : 1 to 10 ) 60

--------------------------------

My problem is that the quantity displayed for the previous fiscal year equals 0 when I select one fiscal year.

If I select a fiscal year and the previous fiscal year, the data are available.

I want to quantities for both fiscal years when I select just one fiscal year but I don't know what's wrong with my expressions .

Can anyone help me it's very important ?

Thanks in advance !

Naw.

[brenner_martina]

Hi,

for Set analysis you have to enter your expression in a varable:

varMaxFiscalYear =Max(Fiscal_Year)
varMaxFiscalMonth =Max(Fiscal_Month)

dito for Year-1 or Year+1, then enter

Sum({<Fiscal_Year= {"=$(varMaxFiscalYear)"}, Mois_Exercice = {"<=$(varMaxFiscalMonth)"} >}Quantity)

[jagan · Accepted solution]

hI,

First declare a variable to calculate prior year as below
PriorYear = Max(Fiscal_Year)-1

Now use the variable in your expression as given below
Sum({<Fiscal_Year= {'$(=PriorYear)'}, Mois_Exercice = {"<=$(=Max(Fiscal_Month))"} >}Quantity)

Hope this helps u.

Regards,

Jagan.

[#4]

Thank you for your reply.

Unfortunately, it doesn't work !

Now I have data displayed but it's not the good ones. For the prior year, I have data of the year selected (prioryear +1). I don't understand why .

I've put all the expressions MAX(..) on variables like you suggested.

Here is a screenshot of the application.

For the record :

When Annee_Exercice= 2008 --> Fiscal Year = 2007-2008

When Annee_Exercice = 2009 --> Fiscal Year = 2008-2009

[#5]

In fact I have forgot a variable in my expressions. It works now !

Thank you !

[#6]

I dont think that you have to use variables, I use the following where Cal-Year is a field in a table.

sum({<Cal_Year={$(=Only(Cal_Year)-1)}>} [Cost Value]) - sum({<Cal_Year={$(=Only(Cal_Year)-1)}>} [Platinum Bonus])

[#7]

Hello,

I also have the same problem.

If I select single FY say, 2015-2016, I get hyphen, whereas if I select 2 financial years together like 2014-2015 & 2015-2016, I get correct numbers.

I have one more concern. In my expression, I also need to add a date filter.

sum({<Date={">=$(vLYMinDate)<=$(vLYMaxDate)"}>}Sales)

Could anyone please suggest a solution for this problem?

Thanks,

Vidhya