7 Replies Latest reply: Jul 5, 2011 6:02 AM by Nagaian Krishnamoorthy

# Set analysis and value from column

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

use the name and month as the dimension?

use sum(value) as the expression?

• ###### Set analysis and value from column

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

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

• ###### Set analysis and value from column

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

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

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

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

• ###### Set analysis and value from column

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