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

    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
          Kaushik Solanki

          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
              Sunil Chauhan

              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

            • Re: Extract 12 values
              Anand Chouhan

              Hi,

               

              From my side i think you have to use FirstValue( ) function to calculate first value in the month in your table for the Date field.

               

              In any type of table you can get this data like Pivot, Straight, Table Box  but it will depend how you show the data

               

              Let me know about that.

               

              See the attached sample file.

               

              Rgds

              Anand

              • Re: Extract 12 values
                Anand Chouhan

                Hi,

                 

                See the attached sample file it is for Months top first values and its value see the Table Months First Values

                 

                It is based on your months first values and when your data is based on 12 month it shows first values of perticular month like 12 months, also i suggest if your table has Month field then the second table on which you calculate First value get the months value from this table it self.

                 

                 

                In any type of table you can get this data like Pivot, Straight, Table Box  but it will depend how you show the data

                 

                 

                Let me know about that.

                 

                HTH

                 

                Rgds

                Anand

                • Re: Extract 12 values
                  Stefan Wühl

                  Hi,

                   

                  if you don't need to care about selection state, you could use FirstSortedValue in the script, like already mentioned above.

                   

                  If you do need to care about selection state, you could use this as expression in any chart to filter the first Sequencenumber per Month:

                  sum({<SequenceNumber = {'$(=concat(aggr(FirstSortedValue(SequenceNumber,Date), MonthstartDate),chr(39)&','&chr(39) ))'}>} SequenceValue)

                   

                  I defined MonthstartDate in the script, please see attached.

                   

                  Hope this helps,

                  Stefan

                  • Extract 12 values

                    use:

                    monthstart(date) as name,

                    aggr([Sequence numbers], name) as name1.

                    by doing this you will get only the sequence numbers at the month start.

                    try this.