topic Re: Chart Expression in QlikView

Chart Expression

khasimvali85 — Tue, 03 May 2016 09:40:53 GMT

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.

Re: Chart Expression

ajsjoshua — Tue, 03 May 2016 09:47:30 GMT

Hi,

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

pfa

Re: Chart Expression

sunny_talwar — Tue, 03 May 2016 09:49:55 GMT

May be like this:

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

Re: Chart Expression

Kushal_Chawda — Tue, 03 May 2016 09:50:26 GMT

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)

Re: Chart Expression

Gysbert_Wassenaar — Tue, 03 May 2016 09:56:40 GMT

See attached example.

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

Re: Chart Expression

khasimvali85 — Tue, 03 May 2016 10:35:00 GMT

Hi Sunny,

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

Re: Chart Expression

sunny_talwar — Tue, 03 May 2016 10:38:07 GMT

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

Re: Chart Expression

khasimvali85 — Tue, 03 May 2016 11:00:56 GMT

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

Re: Chart Expression

sunny_talwar — Tue, 03 May 2016 11:02:37 GMT

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

Why don’t my dates work?

Get the Dates Right

Re: Chart Expression

khasimvali85 — Thu, 05 May 2016 03:44:57 GMT

Hi Sunny,

Appreciate you and always really helpful.

Re: Chart Expression

Anonymous — Thu, 05 May 2016 03:55:29 GMT

=sum({<Year={$(max(Year))},Month={$(max(Month))}>} Sales)

=sum({<Year={$(max(Year))},Month={$(max(Month)-1)}>} Sales)

=sum({<Year={$(max(Year)-1)},Month={$(max(Month))}>} Sales)