5 Replies Latest reply: Oct 27, 2017 9:22 AM by Irene Bagnoli RSS

    Automate Set Analysis using Date Fields

    Mrutyunjaya Hiremath

      Hi,

      I have set of Fields;

      Year = 2016,2017

      HalfYear = FY17-H1, FY17-H2

      Quarter = FY17-Q1, FY17-Q2, FY17-Q3, FY17-Q4

       

      And trying to create a straight table using Set Analysis:

      Sum({<Region = {'North'}, Year= 2017>}Sales)

      Sum({<Region = {'North'}, HalfYear= {'FY17-H1'}>}Sales)

      Sum({<Region = {'North'}, Quarter={'FY17-Q4'}>}Sales)


      Is it possible to write ONE expression and the values to be changed based on the above selections (Year, HalfYear, Quarter);

      Hope we can use Getfieldselections() or Getcurrentselections() etc.

      Also, I need to write expressions by comparing two dates:

      Ex: (Sum({<Region = {'North'}, Quarter={'FY17-Q4'}>}Sales  -  Sum({<Region = {'North'}, Quarter={'FY16-Q4'}>}Sales)*100

      How to automate both expressions, so that the values should change in the same object based on ONE Selection.


      Reg,

      Mrutyunjaya

        • Re: Automate Set Analysis using Date Fields
          I. Sorin Rusu

          Hi Mrutyunjaya,


          If you use the field selections, you wouldn't have to use Set Analysis (except for the region).

          • Re: Automate Set Analysis using Date Fields
            Juraj Misina

            Hi,

             

            I think this blog post might help you: Dates in Set Analysis

             

            Best

            Juraj

            • Re: Automate Set Analysis using Date Fields
              Irene Bagnoli

              Hi Mrutyunjaya,

              I think you can solve your first request just using expression Sum({<Region = {'North'}>}Sales) and then changing selection for Year, HalfYear and Quarter directly in layout.

              However, your second request is a YTD analysis and you can build it with the use of variables, for es. vMaxQuarter and vMaxQuarter_m1, which can be populated dinamically with your selections, and replace 'FY17-Q4' and 'FY16-Q4' in set analysis.

              I'm used to define a quarter as a number: 20171 (Q1), 20172 (Q2)... That's useful in variables and I can define vMaxQuarter as

              year((v_max_date))*10+((month((v_max_date))-if(mod(month((v_max_date)),3)=0,3,mod(month((v_max_date)),3)))/3+1)

              and then vMaxQuarter_m1 as vMaxQuarter-10.

              Here I suppose I have a date field to select so that v_max_date can be max(datefield) or only(datefield), which changes according with selections. If you have not, you have to determine the 2 variables above through the analysis of the text 'FY17-Q4' selected ...

              I hope it helps.

              Bye

              IB

                • Re: Automate Set Analysis using Date Fields
                  Mrutyunjaya Hiremath

                  Hi Irene,

                  Thanks...that is really helpful.

                   

                  The first part..YES you are right & it works the way you said (I added it for the ease of understanding)

                  I am actually looking for the second part - where I have created 50+objects for Quarterly views using;

                  Ex: (Sum({<Region = {'North'}, Quarter={'FY17-Q4'}>}Sales  -  Sum({<Region = {'North'}, Quarter={'FY16-Q4'}>}Sales)*100,

                  And added $(vCurrentQuarter) & $(vPrevQuarter) wherein I always get the values of 'CurrentQuarter' and 'PrevQuarter' based on today().

                  However, I am trying to make the same objects dynamic based on Selections (Year, HalfYear, Quarter) Else I will end up creating

                  50 more objects for Year

                  50 more objects for Halfyear


                  Please suggest how can I automate it better.

                  Reg,

                  Mrutyunjaya



                    • Re: Automate Set Analysis using Date Fields
                      Irene Bagnoli

                      Hi Mrutyunjaya,

                      I attach a qvf file example with sample data to show you exactly what I mean and what I think could be done.

                      In the qvf you will find just one sheet: up a list box with Year, YearMonth and Quarter available for direct selection; down 2 tables. The first table has Total Sales column, Today Q. Sales, Today Q. Sales (Y-1), Max Q. Sales and Max Quarter Sales (Y-1).

                      The last two columns dinamically change with selections.

                      Then, if you'd like just 2 columns and not 4, you could use getselectioncount as you suggested: you will find my example in the second table. Varying selections on Year, YearMonth or Quarter the Sales values will adapt.

                      However set analysis is always based on Quarter field: if you want YTD Analysis even on Month or Year base, you have to create different expressions...

                      One hint, in this situation (but I don't know if this scenario is suitable for you), I prefer reload data till today and so make today coincide with max date, in order to always use "Max" conditions, avoiding the use of getselectioncount or getfieldselection which could cause problems with selections not prevented... 

                      Hope it helps.

                      IB