YTD without date field and month as text

[#1]

Hi All,

I have a column with month names in it. E.g: JAN,FEB,MAR,APR,MAY,..... and a year column which has year names like Y-2014, Y-2015.....

I have added a year and months filter in the sheet. A line chart displays the trend according to months.

If I select a year, it shows all the months corresponding to that year in the chart. But once I select month, only that months data gets displayed in the chart.

I want to show a YTD in the chart i.e. all the months data should be displayed which are less than the selected month.

I do not have a date column here.

How do I do this?

[MK_QSL]

Something like this...

SUM({<InvoiceYear = {"$(=Max(InvoiceYear))"}, InvoiceMonth = {"<=$(=Max(InvoiceMonth))"}>}Sales)

Change fieldname accordinly

[#3]

The month field is not in the form of integer. It is in the form of text. So it is not able to identify which month is greater or smaller than the rest of the months.

Your query may work in case the month field is an integer.

Thanks for replying anyways.

[MK_QSL]

Considering you have Month in MMM format... you can create use Dual in your script...

Dual(InvoiceMonth, Num(Month(Date#(InvoiceMonth,'MMM')))) as InvoiceMonth

Now my above formula should work..

[#5]

Hi Manish,

That's right. But I have to show my trend according to Financial year.

Your code works  for normal month representation i.e. JAN = 1, FEB = 2,.....

I want for Financial year i.e. APR=1,MAY=2,.....

How do I do that?