6 Replies Latest reply: Oct 17, 2011 1:16 AM by Vijit Jindal

# Extract 12 values

Hello,

I have a table with three columns, but over a milion rows. THe columns are: SequenceNumber, Date, SequenceValue.

I would like to create a bar chart where I will have in axis X - Sequence numbers and Sequencevalues as value of the chart. BUt, the problem is that I need first sequence value in every month. So if I have sample data like this:

22222, 2011-01-01, 500

22223, 2011-01-15, 500

22224, 2011-02-01, 500

22225, 2011-02-10, 500

22226, 2011-02-20, 500

22227, 2011-03-02, 500

22228, 2011-03-06, 500

22229, 2011-03-18, 500

etc

Is it possible to extract only sequences 22222, 22224, 22227 and put their values in a chart, because they are the first ones in first three months? I guess that "the only" thing that I have to do is to extract SequenceNumbers, because values will came bu default, but the problem is to write an expression that takes only 12 values based on months.

• ###### Extract 12 values

Hi,

You can use the firstsortedvalue() function to get the first value depending on your sort criteria.

For more search help.

Will post an example tomorrow.

Regards,

Kaushik Solanki

• ###### Re: Extract 12 values

Create a variable name

Variable1

=max(Date,12)

take pivot table

in dimension take

SequenceNumber

and one calculated dimension

=

if(Date>=\$(Variable1) ,Date)  and make spress null on this dimension

take Sequence value in expression( if this not work then take sum(Sequence Value)

see the attached file for you referenec e