topic Re: Comparing periods in line chart in QlikView

Comparing periods in line chart

dafnis14 — Fri, 11 Mar 2016 20:54:41 GMT

Hi,

I need to compare sales periods for a product, for a sale period that crosses 2 subsequent years, in a line chart.

On the X axis i have the sales date in DD-MMM format.

The Year dimension is no good because it will not show the sales for 2 years in the same line.

How can i define the correct period dimension in order to compare them?

Thanks!

Re: Comparing periods in line chart

swuehl — Fri, 11 Mar 2016 21:08:42 GMT

You can use set analysis in your expression to filter your date dimension, something like

=Sum({<SalesDate = {">=$(=Date(AddMonths(Today(),-24),'DD-MMM YYYY'))"}>} Sales)

(assuming that you also have year YYYY in your SalesDate field format).

If this doesn't help, then please post a small sample QVW.

Re: Comparing periods in line chart

HirisH_V7 — Sat, 12 Mar 2016 09:38:27 GMT

Hi,

Can you tell your Date Format,It Contains Year or not,

Example: DD/MM/YYYY

Then you can have a expression like this,

Sum({< Date={‘>=$(=MonthStart(Max(Date), -23))<=$(=Date(Max(Date)))’}>} Sales )

HTH,

Hirish

Re: Comparing periods in line chart

Kushal_Chawda — Sat, 12 Mar 2016 10:01:45 GMT

try

=Sum({<SalesDate = {">=$(=Date(AddMonths(max(SalesDate),-23),'DD-MMM'))"}>} Sales)

Re: Comparing periods in line chart

dafnis14 — Sat, 12 Mar 2016 10:01:46 GMT

Dear People,

Thanks for your kind help.

The business case are selling flights which can be done in a previous year and in the current year.

So it might be helpful to understand.

In case the user select product from previous years then each sale period would be composed of 2 consecutive years which are joined together.

The customer demands that the display on the X-axis would be DD-MMM, because he wants to see a day to day accumulated sales progress.

The original date is DD/MM/YYYY.

Attached is a simple model to demonstrate the issue.

I would like to see the sale period as one line, instead of two for each year.

Thanks!

Re: Comparing periods in line chart

swuehl — Sat, 12 Mar 2016 10:07:28 GMT

Ok, can you somehow outline / sketch how you want to show your two year period in this kind of chart, then?

Maybe using a hand-made sketch drawing?

Re: Comparing periods in line chart

dafnis14 — Sat, 12 Mar 2016 10:37:33 GMT

Hi Swuehl,

It would be one line as shown in this chart, but the dates would be in descending order:

for example: November, December (of previous year), and then January, February (of the current year):

http://screencast.com/t/msOZfqfsZJkX

Thanks!

Re: Comparing periods in line chart

swuehl — Sat, 12 Mar 2016 10:44:17 GMT

So you probably just want to use SalesDate as dimension (not your ResvDayMonth field), maybe formatted as DD-MMM, but keeping the underlying numeric value.

Then limit your SalesDate using one of the set expressions shown above.

This should show you the year range across year bounderies, with SalesDate shown in chronologic correct order.

Re: Comparing periods in line chart

dafnis14 — Sat, 12 Mar 2016 10:57:28 GMT

Hi,

I still can't figure how the second dimension (Year) would be correctly generated.

As we see, it should be merged to one period (e.g., 2014-2015).

Thanks!

Re: Comparing periods in line chart

dafnis14 — Sat, 12 Mar 2016 11:23:10 GMT

Hi Swuehl,

I think the solution is to use the year of the flight sold instead of the year from the order date.

This way, each line is drawn correctly:

2016-03-12_1320 - DafnaLe's library

What do you think?

Re: Comparing periods in line chart

HirisH_V7 — Sat, 12 Mar 2016 11:50:49 GMT

Hi ,

Check this, is this what you required!!,

By creating a Date Field in Script and a Calculated dimension in Chart for only last two years .

=Aggr(Only({<Year={'$(=Max(Year))','$(=Max(Year)-1)'}>} Year),Year)

HTH,

PFA,

Hirish