Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a small problem. I would like to create a chart like this one. But have the months added dynamically based on what monthnumber the users chooses. So if the user chooses month 4 the chart would display months from 1-4., or like the picture shows month 6 is choosen it display from 1-6. So I guess I need an expression for x-axis, but I cant get my head around. All hints would be greatly appreciated.
Regards
Birkir
Hi Birkir,
Dimension: "MonthNum" or "MonthNumShort" (use only the field you want to display)
Measure: use set analysis to limit the period you want to display
e.g.
Dimension: MonthNumShort (1-12)
Measure : Sum(Sales)
-will sum all values according to MonthNum
Sum({$<MonthNumShort={">=1<=$(=Max(MonthNumShort))"}>}Sales)
-will show values from 1 to selected month number
Please check attached qvf.
Regards,
Ronny
You should be able to do this using set analysis. Add the following to your Expression:
{<MonthNum = {"$(='>='& Date(YearStart(Max(MonthNum)), 'YYYY-MM') & '<=' & Date(Max(MonthNum), 'YYYY-MM'))"}>}
User will then be able to make a selection in MonthNum list box to determine what he wants to see 2014-03 will display 2014-01, 2014-02 & 2014-03
HTH
Best,
Sunny
You can do this by using set analysis in your expressions.
Please have a look at this introduction:
The Magic of Set Analysis - Point In Time Reporting • Blog • AfterSync
Basically, you are using something like
=Sum({<Date = {">$(=Yearstart(max(Date)))<=$(=max(Date))"}, Month=, Year= >} Sales)
assuming a calendar with Date, Month, Year.
Hi I have tried what you suggested but I get a invalid dimension . Here below is a set analysis I use in KPI box, sums up to sales to selected month.
=Sum({$<MonthNumShort={">=$(=1)<=$(=max(MonthNumShort))"}>}Sales)
So could I use this for the dimension members?
{$<MonthNumShort={">=$(=1)<=$(=max(MonthNumShort))"}>}
MonthNumShort is the monthnumber or 1,2,3,4,5,6,7,8,9,10,11,12
Hi Birkir,
Dimension: "MonthNum" or "MonthNumShort" (use only the field you want to display)
Measure: use set analysis to limit the period you want to display
e.g.
Dimension: MonthNumShort (1-12)
Measure : Sum(Sales)
-will sum all values according to MonthNum
Sum({$<MonthNumShort={">=1<=$(=Max(MonthNumShort))"}>}Sales)
-will show values from 1 to selected month number
Please check attached qvf.
Regards,
Ronny
Hi,
If you attach sample file then it would be easier to provide solution.
If You have a date field then you can use like below
Sum({<MonthNum=, DateFieldName={{<'>=$(=YearStart(Max(DateFieldName)))<=$(=Date(Max(DateFieldName)))'>}}>} Sales)
Replace MonthNum, DateFieldName, Sales with your actual field names.
Regards,
Jagan.
Hi Ronny
Thank you for this. This solved this for me. I was trying to solve this my filtering the dimension members on the dimension not by using the measure.
Thank you for your help.
Hi All,
Thank you so much for all of your input, it is an honor to be part of such a community. Ronny came up with a demo that explained this to me and I now get my mistake. I was always trying to filter the dimsion members via Set analysis but not use the measure to filter the members.
Thanks again for all of your support.
Kind regards
Birkir
Dear Birkin,
Good to hear that this help you to solve the problem.
Can you please mark the answer as correct and not the reply to the answer.
Regards,
Ronny
Hi,
I have marked your answear as the correct one
Thanks again