Chart Expression

[khasimvali85]

Hi Community,

Hope you are doing well !

I want to discuss about my Query regarding Chart Expression.

I have table fields like Date, Sales,Product.

The Date field contains monthly wise rows not day wise, Sales and Product.(please find attached file).

My Requirement is Create Line chart and its Dimensions should shows like below

Current Month( May-2016) , Previous Month (Apr-2016) and Last Year Current Month (May-2015) and Expression SUM(Sales).

How do i achieve my requirement?

please suggest any one.

Regards,

Khasim.

[ajsjoshua]

Hi,

why you want to show them in dimension rather u can easily show them in expression.

pfa

[sunny_talwar]

May be like this:

=Sum({<Date = {"$(=Date(Max(Date)))", "$(=Date(AddMonths(Max(Date), -1)))", "$(=Date(AddYears(Max(Date), -1)))"}>}Sales)

[Kushal_Chawda]

Data:

LOAD *,

          date(monthstart(Date),'MMM YYYY') as MonthYear;

LOAD Date,

          Product,

          Sales

FROM Table;

Now create the Bar chart

Dimension:

MonthYear

Expression:

1) CurrentMonth

   =sum({<MonthYear={"$(=date(max(MonthYear),'MMM YYYY'))"}>} Sales)

2) PreviousMonth

   =sum({<MonthYear={"$(=date(addmonths(max(MonthYear),-1),'MMM YYYY'))"}>} Sales)

3) LastYearCurrentMonth

    =sum({<MonthYear={"$(=date(addyears(max(MonthYear),-1),'MMM YYYY'))"}>} Sales)

[Gysbert_Wassenaar]

See attached example.

Note, your excel file contained dates in the wrong format.

[khasimvali85]

Hi Sunny,

Thanks for reply, whenever i am trying in my application the given expressions not working.

[sunny_talwar · Accepted solution]

I guess you need to make sure that Date formats match. May be try this:

=Sum({<Date = {"$(=Date(Max(Date), 'YourDateFieldFormatHere'))", "$(=Date(AddMonths(Max(Date), -1), 'YourDateFieldFormatHere'))", "$(=Date(AddYears(Max(Date), -1), 'YourDateFieldFormatHere'))"}>}Sales)

Replace YourDateFieldFormatHere with your DateField Format to make this expression work

[khasimvali85]

Already i mentioned my date format but don't working it shows "No data to display".

[sunny_talwar]

Have you made sure that your date is actually read as date?

Why don’t my dates work?

Get the Dates Right

[khasimvali85]

Hi Sunny,

Appreciate you and always really helpful.