topic Re: Previous Week in QlikView

Previous Week

bharatkishore — Tue, 28 Jun 2016 13:14:50 GMT

Hi All,

I have a week column from excel and i have sales and super sales... Now i need show data for previous week.

The formula for calculating is sales/supersales for previous week.

Can you please help me on this.

Thanks,

Bharat

Re: Previous Week

Anonymous — Tue, 28 Jun 2016 13:18:10 GMT

if you have week as number in Excel upto current week

you Need this Expression:

sum({<yourweekfield={'$(=max()yourweekfield)'}>}sales)

if you have not only actual year you Need mroe set analysis

Re: Previous Week

Anil_Babu_Samineni — Tue, 28 Jun 2016 13:32:59 GMT

Hello,

LOAD

DateField,

Autonumber(Weekstart(DateField), 'Week') as WeekDateField,

... FROM .....

Expressions Are:

1) =Sum({<WeekDateField = {'$(=Max(WeekDateField)-1)'}>}Sales)

Re: Previous Week

Anonymous — Tue, 28 Jun 2016 13:34:32 GMT

sum({<yourweekfield={'$(=max(yourweekfield)-1)'}>}sales)

Re: Previous Week

bharatkishore — Tue, 28 Jun 2016 14:03:19 GMT

Hi Linder

Thanks for your reply..

I need to calculate sales/supersales which should display data for last week.. i m displaying in barchart and it should happen without filters.

Re: Previous Week

Anonymous — Tue, 28 Jun 2016 14:08:12 GMT

what is your Dimension? weekfield

and your Expression?

and your data from Excel contains data sales/supersales from this year

so you may use my Expression: sum({<yourweekfield={'$(=max(yourweekfield)-1)'}>}sales/supersales)

or you define a calulated Dimension: if (yourweekfield = week(today()-1, yourweekfield) and

Expression sum(sales/supersales)

Re: Previous Week

bharatkishore — Tue, 28 Jun 2016 14:20:16 GMT

I have given the following expression:

=sum({<Week={'$(max(Week)-1)'}>}Sales/SuperSales)

but i am getting the value as 0. Can you please let me know if i missed something..

Dimension i have taken as Brand

Re: Previous Week

Anonymous — Tue, 28 Jun 2016 14:25:36 GMT

can you give me some figures so i can try a small sample?

Re: Previous Week

Anonymous — Tue, 28 Jun 2016 14:31:35 GMT

sorry Little mistake (equal sign was missing before max)

=sum({<Week={'$(=max(Week)-1)'}>}Sales/SuperSales

I defined inline table and defined Week as Dimension and above expression

Sales:
load * Inline [
Week, Sales, Supersales
24, 100, 10
24, 150, 5
25, 200, 10
];

Re: Previous Week

bharatkishore — Tue, 28 Jun 2016 14:32:28 GMT

Please find the attached excel.

Re: Previous Week

Anonymous — Tue, 28 Jun 2016 14:43:51 GMT

your weekfield is a datefield, so you Need to define a new field

for example:

Sales:
load * Inline [
Brand, Week, Sales, SuperSales
A, 12.07.2015, 2836, 118
A, 14.12.2015, 2242, 106
A, 21.12.2015, 3751, 147
];

left join(Sales)
load *,
week(Week) as WeekNr
Resident Sales;

then you can use this Expression:

=sum({<WeekNr={'$(=max(WeekNr)-1)'}>}Sales/SuperSales)

you my use eitehr Week or WeekNr as dimension

if you use a mastercalendar (the best choice anyway) you may Need the weekfield from mastercalendar

Re: Previous Week

bharatkishore — Tue, 28 Jun 2016 14:47:00 GMT

Thanks a lot

Re: Previous Week

Tue, 28 Jun 2016 14:48:38 GMT

Hi,

In scripting,

Sales:

LOAD Brand,

Week,

Sales,

[Super Sales],

week(Week) as WeekNum

FROM ....

In expression,

=sum({<weeknum={'$(=max(weeknum)-1)'}>}Sales/[Super Sales])

-Joyson G