Set analysis

[Anonymous]

Hello,

I have this expression which calculate recoveries SUM(PAY_AMOUNT) - Sum ({<PAY_DR_YEAR = P(SALES_DR_YAER)>}PAY_AMOUNT), now i would to know the actually recoveries made in the previous years, so would i be wrong to write these expressions assuming the current Year is 2012;

SUM({$<Year = {2011}>}PAY_AMOUNT) - Sum({$<Year={2011}>}{<PAY_DR_YEAR = P(SALES_DR_YAER)>}PAY_AMOUNT)    and

SUM({$<Year = {2010}PAY_AMOUNT) - Sum({$<Year={2010}>}{<PAY_DR_YEAR = P(SALES_DR_YAER)>}PAY_AMOUNT)   for the previous years respectively.

Thanks

[CELAMBARASAN]

Try this

SUM({$<Year = {$(=Max(Year)-1)}>}PAY_AMOUNT) - Sum({$<Year={$(=Max(Year)-1)},PAY_DR_YEAR = P(SALES_DR_YAER)>}PAY_AMOUNT)    and

SUM({$<Year = {$(=Max(Year)-2)}PAY_AMOUNT) - Sum({$<Year={$(=Max(Year)-2)},PAY_DR_YEAR = P(SALES_DR_YAER)>}PAY_AMOUNT)

[Anonymous]

Is there variable(s) i have to create in your above expressions?

[jagan]

Hi,

No need to create any variables for this, the below expressions are dynamic it calculates the years and uses it in the expression.

Suppose if you select year 2012 then the epxression

=Max(Year)-1 get max selected year 2012 and subtracts 1 from it and returns 2011and that value is used in expression.

=SUM({$<Year = {$(=Max(Year)-1)}>}PAY_AMOUNT) - Sum({$<Year={$(=Max(Year)-1)},PAY_DR_YEAR = P(SALES_DR_YAER)>}PAY_AMOUNT)

    and

SUM({$<Year = {$(=Max(Year)-2)}PAY_AMOUNT) - Sum({$<Year={$(=Max(Year)-2)},PAY_DR_YEAR = P(SALES_DR_YAER)>}PAY_AMOUNT)

Hope this helps you.

Regards,

Jagan.

[Anonymous]

Well the expressions looks okay, but the end result seems the same.Please find attached.Thanks

[jagan]

Hi,

I think = is missed in above expressions. Try below expressions

=SUM({$<Year = {'=$(=Max(Year)-1)'}>}PAY_AMOUNT) - Sum({$<Year={'=$(=Max(Year)-1)'},PAY_DR_YEAR = P(SALES_DR_YAER)>}PAY_AMOUNT)

    and

SUM({$<Year = {'=$(=Max(Year)-2)'}>}PAY_AMOUNT) - Sum({$<Year={'=$(=Max(Year)-2)'},PAY_DR_YEAR = P(SALES_DR_YAER)>}PAY_AMOUNT)

Regards,

Jagan.

[Anonymous]

Hi Jagan,

Thanks for your quick response allow me to ask is the Year suppose to the same year field in the application?

[jagan]

Hi,

Year is the dimension in the file, Please find attached file for solution.

Regards,

jagan.

[jagan]

Hi,

Can you attach the sample file that you are working? 

Regards,

Jagan.