8 Replies Latest reply: May 10, 2018 7:05 AM by Sunny Talwar RSS

    Transaformations based on Quarter

    anusha h

      Hi all,

       

      I have requirement in my project that if we are in the current quarter then display the sum of sales for current month in the current querter. Else display the sum of sales for the latest month in that quarter.

       

      For example, Currently it is 2018 May and quarter 2. So, for quarter 2 the sum of sales should be displayed for only for may. Quarter 1 is alraedy over. So, for quarter 1 the sum of sales should be displayed only for for March.

      In 2017, Q4, sum of sales should be displayed only for Dec and so on.

       

      How can I do it?

        • Re: Transaformations based on Quarter
          Sunny Talwar

          May be create a flag in the script for this

           

          If((MonthStart(QuarterEnd(DateField)) = MonthStart(DateField) and MonthStart(DateField) < MonthStart(Today())) or MonthStart(Today()) = MonthStart(DateField), 1, 0) as Flag

           

          and then use an expression like this

           

          Sum({<Flag = {1}>}Sales)

            • Re: Transaformations based on Quarter
              anusha h

              Hi Sunny,

               

              Thanks for the response and I tried  this.

               

              Now I'm able to see the data for the current month in the current quarter.

               

              But there is one problem here. For the previous quarters, it shows the data only for the last quarter in each year.

               

              Example: I'm able to see the data only for 2017 Q4, 2016 Q4,2015 Q4 and 2016 Q4. Data for rest of the quarters in that year is not displayed.

               

              I want the sum(Sales) for the latest month in each quarter.

               

              Example:

              2018 Q2- should display the data for May at this point and it is working.

              2018 Q1- should display the data for March 2018.

              2017 Q4 - Dec 2017

              2017 Q3 - Sep 2017

              2017Q2 - June 2017 and so on.

               

              Could you please help me with this?

            • Re: Transaformations based on Quarter
              Duncan Blaine

              Hi Anusha

              You could also do the followin

               

              1. Add CalMonthStart to your Calendar object in your script. I assume you have one if you are using Quarters as a filter. eg.

                       

                      LOAD ....

              MonthStart(TransactionDate) as CalMonthStart

              Resident ..

               

              2. Then calculate Sales as


              =Sum({<CalMonthStart={'$(=Max(MonthStart(TransactionDate)))'}>}Sales)

               

              This will always show sales for only the latest month that has recorded sales (in selected Quarter), which should be fine unless you're recording future sales.

              Hope this helps.