Calculation of YTD on cumulative fiscal month based on Month selection?

[#1]

Hello Friends,

I worked direcly with fsmonth column in the following example, It gives the currect values. ( See the text box : "Expression using fsmonth")

But when iam trying to achieve the same results with MonthName field, it gives different results. (See the text box: "Requirement")

MonthName, fsmonth

Jan, 3

Feb,1

Mar, 2

Requirement:

If the user selects MonthName- Jan, The Requirement text box should show the sum(Sales) for all the months ( because fsmonth is 3 for January)

-- Ex:  #sum(sales) where fsmonth<=3

If the user selects MonthName=Feb, The Requirement text box should show the Sum(Sales) for Feb only( Because fsmonth is 1 for February)

--Ex: Sum(Sales) where fsmonth<=1

If the user selects MonthName=Mar, The Requirement text box should show the Sum(Sales) for Feb  & March ( Because fsmonth is 2 for March)

--Ex: Sum(Sales) where fsmonth <=2

Please help me to correct the expression in the Requirement Text box:

Regards

Siva

[MayilVahanan]

Hi

PFA

[#3]

Thanks a lot for your quick response.

fsmonth will not be available on dashboard to make selections on fsmonth.

The only selected fields are Year and MonthName.

if i select MonthName, it should internally map to fsmonth value and this is going input  for the fsmonth in Set Analysis.

For Ex:

=sum({$<fsmonth={"<=$(=fsmonth)"}>}  sales) & ' Sum of Sales'

Instead of using fsmonth(Underlined in the above statement), can we get this value based on selection made in list box of "MonthName".

Expecting the results like this ( for your reference):

If i select  year=2010,MonthName=Jan   --> The associated value is 3 for the this selected month(Jan'2010)

i want the sum(Sales) according to fsmonth <=3 ( I mean Cumulative value :  10+20+5+20+30+30+40)

If i select  year=2010,MonthName=Feb   --> The associated value is 1 for the this selected month(Feb'2010)

i want the sum(Sales) according to fsmonth <=1 ( I mean Cumulative value :  20+30)

If i select  year=2010,MonthName=Mar  --> The associated value is 2 for the this selected month(Mar''2010)

i want the sum(Sales) according to fsmonth <=2 ( I mean Cumulative value :  20+30+30+40)

In real time scenario, we need to count the sum(sales) according the financial year(Apr to Mar)

The fsmonth value for Apr is 1, May is 2 ......Mar is 12

if user selects Apr from Monthname, i want to get the results sum(sales) for the month of Apr

if user selects MAY from Monthname, i want to get the results sum(sales) for the month of Apr and May.

if user selects Jun from monthname, i want to get the results sum(sales) for the month Apr, May and Jun.

.....

This expression is NOT working properly in the "Requirement" text box

=sum({$<year={"$(=GetFieldSelections(year))"},fsmonth={"<=$(=dual(fsmonth,'$(=GetFieldSelections(MonthName))'))"}>}sales)

Iam not sure, whether the above expression is correct. Please correct me, if i am wrong?

Hope this helps you to understand my requirement....

Regards

Siva

[#4 · Accepted solution]

Hi,

I found solution using set analysis, use this expression:

=sum({$<year={"$(=GetFieldSelections(year))"},fsmonth={"<=$(=dual(fsmonth,'$(=GetFieldSelections(MonthName))'))"},MonthName = > } sales) 

Niranjan M.

[#5]

Niranjan,

Excellent.

You are master in Set Analysis.

Thanks a lot. You really made my day.

Regards

Siva