12 Replies Latest reply: Jan 19, 2015 2:57 AM by judas mositsa RSS

    Set analysis

      Hi guys,

      i have year,quarter and month fields in my data source. I need set expression for the below cases:

      1. current year vs last year

      2. Current year quarter vs last year same quarter. For Ex: FY14Q1 vs FY13Q1

      3. Current Quarter vs previous quarter of same year

       

      thanks

        • Re: Set analysis
          Nikhil Garg

          If you want to find the sum of sales then you can do like:

          Take bar chart and then in expression:

           

          Sum({<Year = {Max(Year)}>}Sales) // For current year.

          Sum({<Year = {Max(Year)-1}>}Sales) // For last year.

           

          And likewise other expressions. And also see to the syntax.

          • Re: Set analysis
            Sudhanshu Shrivas

            Hi Viresh,


            This might help.


            • YTD (Year-To-Date) Sales:
              • Sum({$<MonthID = {“<=$(=Max(MonthID))”},
                Year = {$(=Max(Year))},
                Quarter = ,
                Month = >} Sales)
            • QTD  (Quarter-To-Date) Sales:
              • Sum({$<MonthID = {“<=$(=Max(MonthID))”},
                QuarterID = {$(=Max(QuarterID))},
                Year = ,
                Quarter = ,
                Month = >} Sales)
            • MTD (Month-To-Date) Sales:
              • Sum({$<MonthID = {$(=Max(MonthID))},
                Year = ,
                Quarter = ,
                Month = >} Sales)
            • Previous Month Sales:
              • Sum({$<MonthID = {$(=Max(MonthID) – 1)},
                Year = ,
                Quarter = ,
                Month = >} Sales)
            • Previous Quarter Sales:
              • Sum({$<QuarterID = {$(=Max(QuarterID) – 1)},
                Year = ,
                Quarter = ,
                Month = >} Sales)
            • Sales for the same Month but Previous Year:
              • Sum({$<MonthID = {$(=Max(MonthID) – 12)},
                Year = ,
                Quarter = ,
                Month = >} Sales)
            • Sales for same Quarter of the Previous Year:
              • Sum({$<QuarterID = {$(=Max(QuarterID) – 4)},
                Year = ,
                Quarter = ,
                Month = >} Sales)
            • YTD Sales for Previous Year
              • Sum({$<MonthID = {“<=$(=Max(MonthID) – 12)”},
                Year = {$(=Max(Year) – 1)},
                Quarter = ,
                Month = >} Sales)
            • Sales for Rolling 12 Months:
              • Sum({$<MonthID = {“>=$(=Max(MonthID) – 11)<=$(=Max(MonthID))”},
                Year = ,
                Quarter = ,
                Month = >} Sales)

            Thanks

             

            Sudhanshu shrivas

              • Re: Set analysis

                thank you

                • Re: Set analysis

                  Hi Shrivas,

                   

                  I am having the same problem of calculating the dates and I have used the expression you have just pasted but I am not getting any result....it populates zeros on the fields and dashes on other fields like LYTD and YTD. is the anything that I am doing it wrong? below is the expressions that I have created:

                  MTD:

                  Sum({$<MonthId = {$(=Max(MonthId)– 1)}, Year = , Quarter = , Month = >} Counter_Num)

                  PMTD:

                  Sum({$<MonthId = {$(=Max(MonthId) – 1)},Year = ,Quarter = ,Month = >} Counter_Num)

                   

                  Prior Year MTD:

                  Sum({$<MonthId = {$(=Max(MonthId) – 12)},Year = ,Quarter = ,Month = >} Counter_Num)

                   

                  YTD:

                   

                   

                   

                  Sum({$<MonthId = {“<=$(=Max(MonthId))”},Year = {$(=Max(Year))},Quarter = ,Month = >} Counter_Num)

                   

                  LYTD:

                   

                   

                   

                   

                  Sum({$<MonthID = {“<=$(=Max(MonthID) – 12)”},Year = {$(=Max(Year) – 1)},Quarter = ,Month = >} Counter_Num)

                   

                  QTD:

                  Sum({$<MonthID = {“<=$(=Max(MonthID))”}, QuarterID = {$(=Max(QuarterID))},
                  Year = ,
                  Quarter = ,
                  Month = >} Counter_Num)

                  PrevYear_QTD:

                   

                   

                   

                   

                      • Sum({$<QuarterID = {$(=Max(QuarterID) – 4)},
                        Year = ,
                        Quarter = ,
                        Month = >} Counter_Num)

                   

                   

                • Re: Set analysis

                  thanks for the reply.

                  • Re: Set analysis

                    Hi Guys,

                     

                    can anyone help please.....