Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi,
I found solution using set analysis, use this expression:
=sum({$<year={"$(=GetFieldSelections(year))"},fsmonth={"<=$(=dual(fsmonth,'$(=GetFieldSelections(MonthName))'))"},MonthName = > } sales)
Niranjan M.
Hi
PFA
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
Hi,
I found solution using set analysis, use this expression:
=sum({$<year={"$(=GetFieldSelections(year))"},fsmonth={"<=$(=dual(fsmonth,'$(=GetFieldSelections(MonthName))'))"},MonthName = > } sales)
Niranjan M.
Niranjan,
Excellent.
You are master in Set Analysis.
Thanks a lot. You really made my day.
Regards
Siva