topic Set analysis and value from column in QlikView

Set analysis and value from column

Tue, 05 Jul 2011 08:52:47 GMT

I have one table:

LOAD * INLINE [

name,month,value

paul,one,10

rom,one,20

michael,one,30

paul,two,100

rom,two,200

michael,two,300

];

I created pivot table:

name | zaz | sum({$<month={'one'}>} value)

-------------------------------------------------------------------------

michael| one | 10

paul | one |20

rom | one |30

I want to modify my formula:

sum({$<month={$(zaz)}>} value)

but it is not working.

Correct action of formula:

michael| one | 10

paul | one |20

rom | one |30

paul | two |100

rom |two | 200

michael | two | 300

Set analysis and value from column

Tue, 05 Jul 2011 08:59:42 GMT

use the name and month as the dimension?

use sum(value) as the expression?

Set analysis and value from column

Tue, 05 Jul 2011 09:10:49 GMT

This is correct solution, but in my problem i must use dynamic set analysis. I have more complicated pivot table in my project.

Set analysis and value from column

kaushiknsolanki — Tue, 05 Jul 2011 09:12:06 GMT

Hi,

Do you mean you want the output as

michael| one |30

paul | one |20

rom | one |10

paul | two |100

rom |two | 200

michael | two | 300

Then only thing you have to do is, Create chart with dimensions as Name and Month.

Expression as sum(Values)

Regards,

Kaushik Solanki

Set analysis and value from column

Tue, 05 Jul 2011 09:12:59 GMT

then you may need to describe ur problem/objective in detail...

Set analysis and value from column

Tue, 05 Jul 2011 09:35:25 GMT

Ok.

I have two tables:

LOAD * INLINE [

name,month,value

paul,1,10

rom,1,20

michael,1,30

paul,2,100

rom,2,200

michael,2,300

paul,3,1000

rom,3,2000

michael,3,3000

paul,4,10000

rom,4,20000

michael,4,30000

];

LOAD * INLINE [

name,startmonth

paul,3

rom,4

michael,2

];

And one pivot table:

name,zaz,sum({$<month={$(>=zaz)}>} value)

michael,2,33300

paul,3,11000

rom,4,20000

Set analysis and value from column

kaushiknsolanki — Tue, 05 Jul 2011 09:44:50 GMT

Hi,

First thing you can not directly use the field name in set analysis to compare with other field.

If you want to have o/p as you said. .i.e

michael,2,33300

paul,3,11000

rom,4,20000

You need to create a chart with Name, Startmonth as dimension and

Sum(Value) as Expression

Regards,

Kaushik Solanki

Re: Set analysis and value from column

nagaiank — Tue, 05 Jul 2011 10:02:11 GMT

Kaushik's solution is correct and will work if the expression is changed to

Sum(If(month >= startmonth,value,0))