topic Re: Re: How to trend Current Sales vs Prev Sales, using date as dimension? in QlikView

How to trend Current Sales vs Prev Sales, using date as dimension?

bill_mtc — Fri, 02 May 2014 11:55:02 GMT

Hi,

I have here one of client's requirement, would this be possible? Can we able to trend in one line chart the Current Sales vs Prev Sales, using Date as Dimension? (see attached file)

NOTE:

Blue Line Dates are Dec 29, 2013 to Jan 7, 2014

Orange Line Dates are Dec 29, 2012 to Jan 7, 2013

What did is that, only date of Blue Line being used as dimension. This the same as getting Sales Revenue of Same Day of Previous Year.

As far as I'm concern, this is not doable, isn't it? If anyone could help, I would appreciate.

Regards,

Bill

Re: How to trend Current Sales vs Prev Sales, using date as dimension?

Fri, 02 May 2014 11:59:15 GMT

In Line chart , select :

Dimension; Date

Expression 1; Sum(${<Year = $(=max(Year))>}Sales)

Expression 2; Sum(${<Year = $(=max(Year)-1)>}Sales)

Re: How to trend Current Sales vs Prev Sales, using date as dimension?

Gysbert_Wassenaar — Fri, 02 May 2014 12:02:33 GMT

You can try using two expressions:

sum(Sales)

above(sum(Sales)).

This approach does have some drawbacks. See this document for more information: Calculating rolling n-period totals, averages or other aggregations

Re: How to trend Current Sales vs Prev Sales, using date as dimension?

manideep78 — Fri, 02 May 2014 12:03:40 GMT

Hi Bill Ringer Salalima

Make only date as dimension. (for eg, Jan-2, Jan-3 etc)

Then write two expressions for current year and previous year.

Hope this helps

Regards,

Manideep

Re: How to trend Current Sales vs Prev Sales, using date as dimension?

bill_mtc — Sat, 03 May 2014 02:27:10 GMT

Let's say

For Blue Line, dates are from Dec 29, 2013 to Jan 7, 2014

For Orange Line, dates are from Dec 29, 2012 to Jan 7, 2013

Is this possible to have this chart? The dimension used is Date in Blue Line.

Re: How to trend Current Sales vs Prev Sales, using date as dimension?

bill_mtc — Sat, 03 May 2014 03:38:24 GMT

Hello Manideep,

The client wants to select dates freely, there should be date ranges selection. Say date selected is from Dec 1, 2013 to Jan 31, 2014.

So what will happen in the in line chart is that:

Dates to be shown as Dimension is from Dec 1, 2013 to Jan 31, 2014
Sales Revenue will get/calculate daily sales from Dec 1, 2013 to Jan 31, 2014
Previous Sales Revenue will get/calculate sales from Dec 1, 2012 to Jan 31, 2013

Meaning, data points for Previous Sales Revenue will be within the data points of Sales Revenue.

I know this requirement is kinda tricky, but I hope there's solution on this!

Regards,

Bill

Re: Re: How to trend Current Sales vs Prev Sales, using date as dimension?

vikasmahajan — Sat, 03 May 2014 04:50:00 GMT

Dear Bill,

You require like wise comparison for current date vs last yr same day date , Please refer demo attached.

Best of Luck.

Vikas

Re: Re: How to trend Current Sales vs Prev Sales, using date as dimension?

bill_mtc — Sat, 03 May 2014 06:16:56 GMT

Hello Vikas,

I thought attached file could have sample report like what is being required. Anyway, thanks for that info.

Re: How to trend Current Sales vs Prev Sales, using date as dimension?

vikasmahajan — Sat, 03 May 2014 06:47:29 GMT

Dear Bill,

Add this variables in calendar ,

num(Year(TempDate)&num(Month(TempDate),'00')) as YEAR_MONTH, // Link between tables for previous month / year selection

num((Year(TempDate)-1)&num(Month(TempDate),'00')) AS YEAR_MONTH_PREVIOUS, // Link between tables for previous month / year selection

num((Year(TempDate)-2)&num(Month(TempDate),'00')) AS P_YEAR_MONTH_PREVIOUS

Use this variables in set analysis

for CY

SUM({$<FiscalYear={$(=max(FiscalYear))}>} [Sales Amount])

for LY Same Date

( SUM({$< FiscalYear={$(=max(FiscalYear)-1)},YEAR_MONTH=p(YEAR_MONTH_PREVIOUS),[Posting Date]={'<=$(vLMaxDate)'},FinancialYear=,MonthName=>}[Sales Amount])

Hope this helps you

Vikas

Re: How to trend Current Sales vs Prev Sales, using date as dimension?

bill_mtc — Sat, 03 May 2014 07:26:43 GMT

Hi,

I appreciate your effort but I tried to apply, I'm still getting the same output as mine... The requirement is to have like this output (see image). The question is, is it really doable? As you can see in X-axis, date ranges from Dec 29, 2013 to Jan 7, 2014. So here, two Years are involve.

Blue Line is data of Sales as of Dec 29, 2013 to Jan 7, 2014

Orange Line is data of Sales as of Dec 29, 2012 to Jan 7, 2013

I would more appreciate if you could provide sample report that display like chart below? My apology of posting the same image.

Re: Re: How to trend Current Sales vs Prev Sales, using date as dimension?

Sat, 03 May 2014 13:13:40 GMT

Based upon those date requirements the lines would never intersect you have two distinct Years and a summary for each.

If the Yellow runs to December 2013 and the Blue runs from December 2013 how would they ever cross?

You would only be able to show Jan - Dec vs Jan to Dec of the relevant years (or Qtrs or which ever date period you select)

as attached - modified from Vikas

Re: Re: How to trend Current Sales vs Prev Sales, using date as dimension?

vadimtsushko — Sun, 04 May 2014 08:43:10 GMT

Hi, Bill Ringer Salalima

The question is, is it really doable?

I believe answer is yes. I would prefer to use for it AsOfTable approach as in article Calculating rolling n-period totals, averages or other aggregations mentioned by Gysbert Wassenaar in his answer.

I believe it gives results conforming to your requirements.

Look at example in attachment

Re: Re: How to trend Current Sales vs Prev Sales, using date as dimension?

bill_mtc — Mon, 05 May 2014 02:17:23 GMT

Hello Vadim,

I already check the attached file, upon checking, I find it helpful. Will have to appy it first to the application and whenever it solves the problem, I will mark your answer as correct. Thank you so much!

Regards,

Bill

Re: How to trend Current Sales vs Prev Sales, using date as dimension?

bill_mtc — Tue, 06 May 2014 09:28:08 GMT

Thank you everyone for participating this ticket/discussion. Vadim Tsushko got the closest answer. I applied his idea, and add some tweakings, until I got the correct answer. (see attached)

Re: How to trend Current Sales vs Prev Sales, using date as dimension?

stevedark — Sat, 31 May 2014 07:01:56 GMT

Hi there,

I produced a blog post outlining how you can accumulate values across dimensions using multiple joins to the data model and uploaded an QlikView document showing the code working:

QlikView App: Accumulate values in the Data Model

The blog post explains how to create an accumulated figure, a moving annual total and an average bar. The method used is similar to the one given here for prior periods.

Enjoy.

Steve

Re: How to trend Current Sales vs Prev Sales, using date as dimension?

Anonymous — Mon, 01 Dec 2014 22:56:28 GMT

Hi Bill

Can you kindly share the application.

Regards

Re: How to trend Current Sales vs Prev Sales, using date as dimension?

bill_mtc — Tue, 17 Mar 2015 08:43:22 GMT

Yes sure, I will create a sample application related to the topic. Thanks for asking!

Regards,

Bill