topic Re: Compare Sales for Current Period vs Previous Period in QlikView

Compare Sales for Current Period vs Previous Period

inessa_zhurba — Fri, 12 Oct 2018 19:22:08 GMT

This is what I am trying to do. I have a report below is a picture.

Percentages suppose to show by month increase of Sales.

Underlying data contains 3 years worse of data.

I was try to get data from sql. I am not sure how to do it in qlik. Would you share a script with me?

Thanks,

Inessa.

Re: Compare Sales for Current Period vs Previous Period

dplr-rn — Fri, 12 Oct 2018 19:30:12 GMT

Use before function.

e.g. (Sum(Sales) - before(Sum(Sales)) ) / before(Sum(Sales))

so in Feb column before(Sum(.... gives value of sum from jan

check below for more details

Missing Manual - Before() and After()

Re: Compare Sales for Current Period vs Previous Period

inessa_zhurba — Wed, 17 Oct 2018 19:30:27 GMT

Thank you so much!!!

Re: Compare Sales for Current Period vs Previous Period

dplr-rn — Wed, 17 Oct 2018 19:32:52 GMT

no problem. close the thread so that others can see the answers too

Re: Compare Sales for Current Period vs Previous Period

inessa_zhurba — Wed, 17 Oct 2018 19:42:39 GMT

Hello again - It works as a charm for all months started with February(month #2). It doesn't look like it works for the month #1 (January). It should compare to December previous year - but there are blanks on a report:

Re: Compare Sales for Current Period vs Previous Period

dplr-rn — Thu, 18 Oct 2018 13:36:41 GMT

that becomes a bit more convoluted

check below. basically what i am doing to is if its first column go to last column in previous line. Also simplified function (A-B) /B as A/B -1

if(ColumnNo()=1,Sum(Sales)/above(after(Sum(Sales),11)) -1, Sum(Sales)/before(Sum(Sales)) -1)

stalwar1‌ any straightforward way of doing this with set analysis?

Re: Compare Sales for Current Period vs Previous Period

sunny_talwar — Thu, 18 Oct 2018 13:40:25 GMT

I would use Aggr() with MonthYear field to get this

Aggr(Sum(Sales)/Above(Sum(Sales)) - 1, (BillingMonthYear, (NUMERIC)))

Where BillingMonthYear is created in the script something in this fashion

Date(MonthStart(DateField), 'MMM-YYYY') as BillingMonthYear

Re: Compare Sales for Current Period vs Previous Period

inessa_zhurba — Thu, 18 Oct 2018 22:20:15 GMT

Thank you Sunny and Dilip both very much!! Both approaches are working!!!

I have a new challenge - I need to compare This Year Month to a Previous Year Month

And I have a Numeric field that Identifies a BillingPeriod. Example:

BillingPeriodID 201501 stands for January of 2015

I was trying to mimic last post by Sunny:

Aggr(Sum(AmountBilled)/Above(Sum(AmountBilled)) - 12, (BillingPeriodID, (NUMERIC)))

It didn't work though!

Here how report looks like:

Would you be able to help me with the script?

Thank you!!

Re: Compare Sales for Current Period vs Previous Period

sunny_talwar — Fri, 19 Oct 2018 00:04:02 GMT

Try this

Aggr(Sum(Sales)/Above(Sum(Sales), 12) - 1, (BillingMonthYear, (NUMERIC)))

Re: Compare Sales for Current Period vs Previous Period

inessa_zhurba — Fri, 19 Oct 2018 20:52:34 GMT

Hi Sunny - thank you very much for your reply! Unfortunately it doesn't work.

Here is a data that I have by Year and month:

This is what needs to happen:

For example for Month of January 2016 (BillingPeriodID = 201601) it should display data from January 2015 (BillingPeriodID = 201501) (Previous Year and the same Month Number)

I was trying to play with it - no success! Would you be able to take a second look, please?

Thank you!!

Re: Compare Sales for Current Period vs Previous Period

inessa_zhurba — Mon, 22 Oct 2018 15:56:50 GMT

Hi Sunny - here are more details:

The expression that you have suggested works in some cases , but not always.

(Aggr(Above(sum(AmountBilled), 12)-1, (BillingPeriodID, (NUMERIC))))

This is how chart looks like below - there are empty cell on a pivot , that should have data in:

Am I missing anything?

Thank you!!

Re: Compare Sales for Current Period vs Previous Period

sunny_talwar — Mon, 22 Oct 2018 16:24:55 GMT

Are you making selections?

Re: Compare Sales for Current Period vs Previous Period

inessa_zhurba — Mon, 22 Oct 2018 16:45:37 GMT

Hi Sunny - there are filters exists on a report for a Billing Year to be selected if needed.

Report defaults to the past 3 years (starting from 01/01/2015).

Re: Compare Sales for Current Period vs Previous Period

inessa_zhurba — Mon, 22 Oct 2018 17:14:37 GMT

Below are two tables to compare on the same report:

First have sum(AmountBilled) Bi Month/Year where measure expression simply: =Sum(AmountBilled):

You see that all cells are populated.

Second report (below) should display sum(AmountBilled) for the same month, but previous year. It does display correct data in some cases: Jan - April 2016 for example display data for Jan- Apr 2015 (as you see sum(AmountBilled) is the same):

But - for May thru July 2016 there is no data that should have been display for the same months and 2015 as it show on a previous table.

Expression that I am using on a second table is:

=(Aggr(Above(sum(AmountBilled), 12)-1, (BillingPeriodID, (NUMERIC))))

Does it make sense? Thank you!!

Re: Compare Sales for Current Period vs Previous Period

sunny_talwar — Mon, 22 Oct 2018 18:09:54 GMT

How about you try this

Above(Sum(AmountBilled))

Re: Compare Sales for Current Period vs Previous Period

evan_kurowski — Mon, 22 Oct 2018 20:30:16 GMT

Is it considered cheating to script this? Good results with minimal expression chart syntax, if scripting is allowed an additional column with Peek() statement. (**Limitation: You wouldn't be able to split pre-calculated aggregates with end-user UI selections)

Re: Compare Sales for Current Period vs Previous Period

inessa_zhurba — Tue, 23 Oct 2018 20:29:56 GMT

Thanks Sunny ! I fixed that!! It is all good!

Re: Compare Sales for Current Period vs Previous Period

inessa_zhurba — Tue, 23 Oct 2018 20:30:40 GMT

Thanks Evan for looking into it!!

Re: Compare Sales for Current Period vs Previous Period

evan_kurowski — Wed, 24 Oct 2018 14:12:16 GMT

Sorry, forgot to include the script that generated those visualizations. Naturally this data sample would need adapted to your own data modeling.

FOR vMonth = 0 to 47

//CONCATENATE(MONTHS)

[MONTHS]:
LOAD
$(vMonth) AS ORDINAL_MONTH,
AddMonths(MakeDate(2015,1,1),$(vMonth)) AS MONTH_START,
Num(round(Rand() * 10000,.01),'$#,###.00') AS MONEY,
Peek(MONEY) AS PRIOR_MONEY
AUTOGENERATE(1);

NEXT

Re: Compare Sales for Current Period vs Previous Period

inessa_zhurba — Thu, 25 Oct 2018 16:28:33 GMT

Thank you!!