6 Replies Latest reply: Dec 10, 2015 12:00 AM by Kumar Pramod RSS

    Set analysis expression

    Kumar Pramod

      Hi all,

       

      I have a Inline script like below,

      No_of_days_in_monthPeriod:

      LOAD * INLINE [

        Monthperiods , no_of_days

        Dec-15,28

        Jan-16,35

        Feb-16,28

        Mar-16,28

        Apr-16,28

        May-16,35

        Jun-16,28

        Jul-16,35

        Aug-16,28,

       

        ];

       

      I need to get the no_of_days for particular Monthperiods  using set analysis,

      i wrote set analysis like below but not working,

      If(match(MonthPeriods={"$(=max(MonthPeriod1))"},no_of_days))

       

      max(MonthPeriod1) = 'Dec-15'

      I need the value of no_of_days when max(MonthPeriod1)= MonthPeriods.


      Can anyone explain and help where i am doing wrong


      Thanks,

      Kumar

        • Re: Set analysis expression
          Tresesco B

          In a text box:

          =FirstSortedValue(no_of_days, -date#(Monthperiods, 'MMM-YY'))

           

          and if there are multiple data points for max period, try with sum() like:

           

          =Sum({<Monthperiods={"$(=Date(max(date#(Monthperiods, 'MMM-YY')),'MMM-YY'))"}>}no_of_days)

           

          Note, the date#() might not be required if your period is having proper date values at the backend, i.e.- it is a dual.

            • Re: Set analysis expression
              Kumar Pramod

              Hi Tresco,

               

              Actually i am using one set expression like below:

              To Calculate Projected use for current month period.

               

              ((count({$<MonthPeriod={"$(=Month(Today()))"}, DateType= {'accessed'}>} invitation_id)

              /

              count({$<MonthPeriod={"$(=max(MonthPeriod))"}>}Distinct CanonicalDate)-1)

              *

              28)

               

              I need the value when, MonthPeriods = max(MonthPeriod)

              For example now max(MonthPeriod) = Dec-15

              next month it will change to Jan-16

               

              So at that i need the value of no_of_days of Jan-16.

               

              I think u understand my Scenario.

                • Re: Set analysis expression
                  Tresesco B

                  Your date-period field in the sample is a text field. So you have to parse the field to get the proper max value. Try the set comparison part as I showed above, like:

                   

                  {<Monthperiods={"$(=Date(max(date#(Monthperiods, 'MMM-YY')),'MMM-YY'))"}>}

                   

                  adjust similarly for today() comparison.

                   

                  If this doesn't work, try to share your sample qvw.

                  • Re: Set analysis expression
                    Tamil Nagaraj

                    Try,

                     

                    ((count({$<MonthPeriod={"$(=Date(Today(),'MMM-YY'))"}, DateType= {'accessed'}>} invitation_id)

                    /

                    count({$<MonthPeriod={"$(=Date(Min(Date#(MonthPeriod,'MMM-YY')),'MMM-YY'))"}>}Distinct CanonicalDate)-1)

                    *

                    28)

                      • Re: Set analysis expression
                        Kumar Pramod

                        HI Nagraj,

                         

                        In above set expression, in the place of 28 i need to write a expression where it should pic dynamically value from the table.

                        So for that i am finding a expression.

                        For Jan-16 it should take 35 instead of 28.

                        LOAD * INLINE [

                          Monthperiods , no_of_days

                          Dec-15,28

                          Jan-16,35

                          Feb-16,28

                          Mar-16,28

                          Apr-16,28

                          May-16,35

                          Jun-16,28

                          Jul-16,35

                          Aug-16,28,

                         

                          ];