topic Re: Cumulative Straight Table in QlikView

Cumulative Straight Table

Thu, 07 Jun 2012 13:16:46 GMT

Date	Daily Performance	Cumulative Performance
01/01/2012	10	10
02/01/2012	15	25
03/01/2012	7	32
...
30/01/2012	15	47
31/01/2012	10	57
01/02/2012	5	5
02/02/2012	10	15
03/02/2012	7	22
04/02/2012	9	31

Hello,

I am looking for some help with set analysis, I need to provide a Cumulative Performance column on performance data.

What I am trying to achieve is displayed above,

With the cumalative performance expression restarting at each month beginning.

Any help would be appreciated,

Thanks

Jeremy

Re: Cumulative Straight Table

lironbaram — Thu, 07 Jun 2012 13:25:53 GMT

hi check this example

Re: Cumulative Straight Table

Thu, 07 Jun 2012 15:04:49 GMT

Thanks for reply!

Unfortunatly, I need the straight table to reflect the 'Cumalative Performance' up to the date and including any date selected.

EG: If i selecte the date 30/01/2012

30/01/2012

The above will be returned.

Jeremy

Re: Cumulative Straight Table

Thu, 07 Jun 2012 23:18:26 GMT

Hi,

Calculating the cumulative values during LOAD might be helpful.

Pls. see the following post:

http://community.qlik.com/message/122935#122935

Pls. let me know if it is not clear, I can provide an example.

Regards,

http://quickdevtips.blogspot.com/

Re: Cumulative Straight Table

Thu, 07 Jun 2012 23:36:53 GMT

Hi,

pls see the attached example.

Regards

http://quickdevtips.blogspot.com/

Re: Cumulative Straight Table

Sokkorn — Fri, 08 Jun 2012 01:37:23 GMT

Hi Jeremy,

Let try this way:

1. Load Data

[Data]:

LOAD * INLINE [

Date , Daily Performance

01-01-2012 , 10

02-01-2012 , 15

03-01-2012 , 7

30-01-2012 , 15

31-01-2012 , 10

01-02-2012 , 5

02-02-2012 , 10

03-02-2012 , 7

04-02-2012 , 9];

2. Create one variable (Settings ==> Variable Overview ==> Add)

vAccumulate = Sum({$<Date = {"<=$(=MAX(Date))"}>} [Daily Performance])

3. Create Pivot Table with

a. Dimension:

1. Date

2. Daily Performance

b. Expression:

1. =RangeSum(Above(Total Sum([Daily Performance]),0,RowNo(TOTAL))) and put IsNull(Date) in Conditional (Enable expression when user clear Date value)

2. =vAccumulate and put NOT IsNull(Date) in Conditional (Enable expression when user select Date value)
4. Play around your data and selection.

See the sample attached file.

Hope this help.

Regards,

Sokkorn