topic Re: sum level higher in straight table in QlikView

sum level higher in straight table

Wed, 14 Sep 2016 14:51:11 GMT

In my case, source table

Level	Header 2
level1	10
level2	4
level3	5
level1	1
level2	3

Difined level relation as level1 => level2 => level3

I need the straight table like blow

Level sum

level1 23 // sum({<Level={'level1','level2','level3'}>} num) 10+1+4+3+5

level2 12 // sum({<Level={'level2','level3}>} num) 4+3+5

level3 5 //sum({<Level={'level3'} num) 5

Thanks for any help.

Re: sum level higher in straight table

sunny_talwar — Wed, 14 Sep 2016 15:39:18 GMT

May be using a Link Table

Table:

LOAD Level,

KeepChar(Level, '0123456789') * 1 as LevelNum,

[Header 2]

FROM

[https://community.qlik.com/thread/232687]

(html, codepage is 1252, embedded labels, table is @1);

LinkTable:

LOAD 'Level' & LevelNum as ReportLevel,

LevelNum + IterNo() - 1 as LevelNum

Resident Table

While LevelNum + IterNo() - 1 <= 3;

Re: sum level higher in straight table

Thu, 15 Sep 2016 03:38:02 GMT

Unfortunately I have some other expression in straight table use dimension "Level",so I can't change the dimension.

It's my mistake that forget attension this condition on the question.

Thanks for your help.

Re: sum level higher in straight table

sunny_talwar — Thu, 15 Sep 2016 10:15:37 GMT

Create a new flag in the script like this and also created another field which I don't want to do an accumulated sum

Table:

LOAD Level,

KeepChar(Level, '0123456789') * 1 as LevelNum,

[Header 2],

Ceil(Rand() * 100) * 10 as Sales

FROM

[https://community.qlik.com/thread/232687]

(html, codepage is 1252, embedded labels, table is @1);

LinkTable:

LOAD 'Level' & LevelNum as ReportLevel,

LevelNum + IterNo() - 1 as LevelNum,

If(LevelNum + IterNo() - 1 = LevelNum, 1, 0) as Flag

Resident Table

While LevelNum + IterNo() - 1 <= 3;

Dimension:

ReportLevel

Expressions

1) =Sum([Header 2])

2) =Sum({<Flag = {1}>}Sales)

Anywhere you want to do a calculation based of Level, use Flag = {1} in your expression

Re: sum level higher in straight table

Fri, 16 Sep 2016 06:54:03 GMT

Thanks very very very much.

I have a long way to go.