I want to use an expression as a dimension for a line chart

[Anonymous]

Hi Guys,

I want to use an expression as a dimension for a line chart, I got variables set as Current month to Previous 12 months, how do I use all of them so I wouldn't have to change them every month, that they will be dramatic.

Cheers

[Anonymous]

No, it needs to be a line chart so they can see the trend.  At the moment, I'm bringing in 25 months worth of data, in my charts, I only want to show the last 12 months.

[Gysbert_Wassenaar]

Then use a field with the month values as dimension.

[Anonymous]

Do I create another table with a where clause for 12 months or is there another way where I can limit it to just the last 12 months. 

[Gysbert_Wassenaar]

You can use a set analysis expression to limit the results to the last 12 months.

Sum({<MyDateField={'>=$(=MonthStart(Today(),-12))<=$)=MonthStart(Today(),-1)))'>}>}Amount)

[Anonymous]

I'm getting no values.

[Gysbert_Wassenaar]

post your app

[Anonymous]

I can't I'm using qs server.

I think the problem may be, my date field is 201511, 201512, 201601 and it doesn't recognise the months.

[Gysbert_Wassenaar]

Very likely. Unless that field is a dual valued field it is useless. 201601 - 1 = 201600, which won't be a value in your data. You need either a date field so you can use MonthStart or AddMonths or a consequetively numbered month number field that also increases values by one across year borders. You may want to create the necessary field in the script.

[Anonymous]

Just say this is the dummy data for six months, six months is loaded in, but I only want to see the last three months on the charts.

DATE SALES	SALES
201507	8494
201507	434
201508	244
201508	3453
201508	33333
201509	5677
201509	32343
201510	23334
201510	3234
201511	2424
201511	7567
201512	445
201512	356
201512	4333
201601	5677
201601	4456 DATE SALES SALES 201507 8494 201507 434 1508 244 201508 3453 201508 33333 201509 5677 201509 32343 201510 23334 201510 3234 201511 2424 201511 7567 201512 445 201512 356 201512 4333 201601 5677 201601 4456

[Anonymous]

Dual valued as in 2016 being year and 01 being month? is this possible?  and will it then be recognisable as a date?