topic Re: Sales Last / Previous Week in QlikView

Sales Last / Previous Week

Anonymous — Fri, 11 Sep 2015 07:39:36 GMT

Hi Guys,

I need an comparisation between Sales current week vs last week. But I need the possibility to select a week so the function above or below doesn't work. Here is my Sales table;

What is the proper set analysis for getting the previous record?

Pls advice

Regards,

Robert

WeekY	Previous Orderportfolio	Current Orderportfolio
2015/37	7.363.248	7.854.268
2015/36	7.038.389	7.363.248
2015/35	7.025.413	7.038.389
2015/34	7.373.356	7.025.413
2015/33	7.319.992	7.373.356
2015/32	6.876.961	7.319.992
2015/31	7.009.264	6.876.961
2015/30	6.666.926	7.009.264

Re: Sales Last / Previous Week

sunny_talwar — Fri, 11 Sep 2015 07:41:48 GMT

This seems like already aggregated (expected) data, can you share raw data?

Re: Sales Last / Previous Week

Anonymous — Fri, 11 Sep 2015 07:56:31 GMT

Hi Sunny,

Yes, sum of

but it's just what i did in Excel. Now i need a set analysis to reproduce the same data.

Can you use this as sample data?

Rob

Re: Sales Last / Previous Week

sunny_talwar — Fri, 11 Sep 2015 07:58:27 GMT

Do you have two columns in your database for Current and Previous period? or is it just one column and this is a kind of display you want to create?

Re: Sales Last / Previous Week

jonathandienst — Fri, 11 Sep 2015 07:58:42 GMT

Set analysis will not help in a row-by-row expression such as previous week. A set expression is evaluated once for the table and not on a row by row basis.

Have a look at Above() or Below() chart functions if you want to do this in a table.

For example, a straight table

Dimension: WeekY
Expression1: your existing expression for current portfolio
Expression2: =Below(Column(1)) for previous portfolio

Re: Sales Last / Previous Week

Anonymous — Fri, 11 Sep 2015 08:05:04 GMT

Yes, correct.

It's just one column (Sales) with the value. Previous is the calculation i need.

Rob

Re: Sales Last / Previous Week

Anonymous — Fri, 11 Sep 2015 08:08:44 GMT

Hi Jonathan,

Thanks for reply.

If i use the functions,I cannot select a certain week (like wk 2015/35), because this will show me zero for previous. column. Logic because I do not have a below or above records...

Re: Sales Last / Previous Week

sasiparupudi1 — Fri, 11 Sep 2015 08:49:32 GMT

Try

t1:

load * Inline

[

WeekY,Current Orderportfolio

2015/37,7.854.268

2015/36,7.363.248

2015/35,7.038.389

2015/34,7.025.413

2015/33,7.373.356

2015/32,7.319.992

2015/31,6.876.961

2015/30,7.009.264

];

NoConcatenate

t2:

LOAD

WeekY,[Current Orderportfolio],if (isnull(Peek([Current Orderportfolio])),0,Peek([Current Orderportfolio])) as [Previous Orderportfolio]

Resident t1 order by WeekY;

drop Table t1;

Re: Sales Last / Previous Week

sunny_talwar — Fri, 11 Sep 2015 08:59:05 GMT

May be this:

Script:

Table:

LOAD *,

Rand() * 100000 as Value;

LOAD Year(Today() - RecNo()) & '/' & Week(Today()-RecNo()) as YearWeek,

Date(Today() - RecNo()) as Date,

Year(Today() - RecNo()) as Year

AutoGenerate 618;

How it looks in Table Box:

Straight Table:

Dimension: YearWeek

Expressions

1) 2015: =Sum({<Date = {"$(='>=' & Date(YearStart(Max(Date))) & '<=' & Date(Max(Date)))"}>}Value)

2) 2014: =If(Sum({<Date = {"$(='>=' & Date(YearStart(Max(Date))) & '<=' & Date(Max(Date)))"}>}Value) > 0, Above(Sum({<Date = , YearWeek = >}Value), 52))

With no selection:

With Selections:

I hope this is what you are looking for.

Best,

Sunny