Filter Months

[surajap123]

Hi All,

I want to show last 6 months and last 12 months data to users using buttons(6 months button and 12 months button).

By default we want to show the complete data.

I have pivot table with Month-Year as a horizontal dimension.

Please help.

[sasikanth]

HI,

create 2 expressions 1 for 12 months & another for 6 Months then enable them using a variable

For last 12 Months:

sum({<Date={ ">=$(=Date(addMonths(max(Date),-12),'DD/MM/YYYY')) <= $(=Date(addMonths(max(Date)),'DD/MM/YYYY'))"}>}Margin)/

sum({<Date={ ">=$(=Date(addMonths(max(Date),-12),'DD/MM/YYYY')) <= $(=Date(addMonths(max(Date)),'DD/MM/YYYY'))"}>}Sales)

For Last 6 months:

sum({<Date={ ">=$(=Date(addMonths(max(Date),-6),'DD/MM/YYYY')) <= $(=Date(addMonths(max(Date)),'DD/MM/YYYY'))"}>}Margin)/

sum({<Date={ ">=$(=Date(addMonths(max(Date),-6),'DD/MM/YYYY')) <= $(=Date(addMonths(max(Date)),'DD/MM/YYYY'))"}>}Sales)

please find the attached application

[surajap123]

Hi Sasi,

Thanks for the solution.

Could you help me with a single expression, instead of conditionally enabling/disabling the expressions.

Thanks!

[sasikanth]

HI,

Create a variable to replace -12/-6

sum({<Date={ ">=$(=Date(addMonths(max(Date), $(vLimit)),'DD/MM/YYYY')) <= $(=Date(addMonths(max(Date)),'DD/MM/YYYY'))"}>}Margin)/

sum({<Date={ ">=$(=Date(addMonths(max(Date),$(vLimit)),'DD/MM/YYYY')) <= $(=Date(addMonths(max(Date)),'DD/MM/YYYY'))"}>}Sales)

Set button trigger as 

for Last 12 months :  vlimit=-12

For Last 6 Month  : Vlimit=-6

[surajap123]

Hi Sasi,

I notice that the expression is showing 1 additional month.

eg- If i select 6 months button i see 7 months.

Do u think, if i change -6 to -5, is the correct approach.

[sasikanth]

hi,

Yes , you can change it to -5  or   change '>=' to '>'

[surajap123]

Thanks for the reply. Sorry to bother again.

I realised that my requirement not just to filter locally in the chart, but in the whole dashboard.

So when user click on 12 months button, the selection has to happen on MonthYear field, so that the whole dashboard is reflected.

I hope this can be achieved by putting the set analysis expression in the button and keeping the chart expression unchanged. ie. sum(Margin)/sum(Sales)

I tried to put the below expression in the button using SelectInField trigger, but it is not working.

>=$(=Date(addMonths(max(Date), $(vLimit)),'DD/MM/YYYY')) <= $(=Date(addMonths(max(Date)),'DD/MM/YYYY'))

Please help!

[satheshreddy]

Hi Suraj,

you can use addmonths() and write the exp you will get it.

Regards

Sathish

[surajap123]

I have written the below expression in a button(to select last 6 months) with action Select In Field-

MonthYear

='>='$(=Date(addMonths(max(Date), -5),'DD/MM/YYYY')) <= $(=Date(addMonths(max(Date)),'DD/MM/YYYY'))

But his is not working. !

Please see bottom chart in the app

[kamalqlik]

Hi Suraj,

Please use the attached application.

I have created a trigger . Go to Document Properties-> Triggers-> On Open, which will show the chart with no default condition.

For other i have used the 6 Month and 12 Months Text object.

Please check and confirm