topic Re: Set analysis question in QlikView

Set analysis question

Sat, 27 Sep 2014 03:32:29 GMT

Hi All,

I have here a scenario which I need to get the sum specific by week.

Ex.

Event	UOM	19-Sep-14 Fri	20-Sep-14 Sat	21-Sep-14 Sun	22-Sep-14 Mon	23-Sep-14 Tue
Order	Unit	2323	234	45364	5675	75
Start	Unit	213	3424	2343	6767	323
Release	Unit	3232	23432	434	342342	232

Event

UOM

19-Sep-14

Fri

20-Sep-14

Sat

21-Sep-14

Sun

22-Sep-14

Mon

23-Sep-14

Tue

Order

Unit

2323

234

45364

5675

Start

Unit

213

3424

2343

6767

323

Release

Unit

3232

23432

434

342342

232

If Event = Order, then it should only sum all the Qty on the last day of the week.

(Date = >=min(Date of that week)<=max(Date of that week)

ex: Date = >=19-Sep-14<=21-Sep-14 ; Date = >=22-Sep-14<=23-Sep-14

How can I include this on my set analysis?

Thanks

Re: Set analysis question

sundarakumar — Sat, 27 Sep 2014 05:15:09 GMT

Guess you should first use cross table to change of data. Set analysis is applied over a single field.

pls go thro

Loading Cross Tables

-Sundar

Re: Set analysis question

Sat, 27 Sep 2014 05:27:43 GMT

Hi,

Dates are not constant, they can be change, I will select what date I want to be last shown in the chart. I can select 22-Sep-14 and the comparison will be like this:

Date = >=22-Sep-14<=22-Sep-14, i will then get the sum on those dates only.

however if i select another date: 20-Sep-14 then:

Date = >=19-Sep-14<=20-Sep-14

Re: Set analysis question

maxgro — Sat, 27 Sep 2014 08:29:44 GMT

sum({$

<Event={Order},Date={">=$(=WeekEnd(max(Date)))<=$(=WeekEnd(max(Date)))"}>

<Event={Start,Release},Date={">=$(=WeekStart(max(Date)))<=$(=WeekEnd(max(Date)))"}>

}

Qty)

Re: Set analysis question

Sat, 27 Sep 2014 08:55:52 GMT

Hi,

Thanks for the idea, but if that is my formula then the weekstart will only compute for that week. i also need the previous to compute for the last week, even the last 2weeks and so on

Re: Set analysis question

jagan — Sun, 28 Sep 2014 03:09:24 GMT

Hi,

For current and last week

sum({$

<Event={Order},Date={">=$(=WeekEnd(max(Date)))<=$(=WeekEnd(max(Date)))"} + {">=$(=WeekEnd(max(Date), -1))<=$(=WeekEnd(max(Date), -1))"}>

<Event={Start,Release},Date={">=$(=WeekStart(max(Date), -1))<=$(=WeekEnd(max(Date)))"}>

}

Qty)

Regards,

Jagan.

Re: Set analysis question

maxgro — Sun, 28 Sep 2014 07:35:59 GMT

you can use the second parameter of weekstart and weekend

from Qlik online help

WeekStart(date [, shift = 0 [,weekoffset = 0]])

Returns a value corresponding to a timestamp with the first millisecond of the first date ( Monday ) of the calendar week containing date. The default output format will be the DateFormat set in the script. Shift is an integer, where the value 0 indicates the week which contains date. Negative values in shift indicate preceding weeks and positive values indicate succeeding weeks. If you want to work with weeks not starting midnight between Sunday and Monday, indicate an offset in days in weekoffset. This may be given as a real number indicating days and/or fractions of a day.