Set analysis

[#1]

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

[nikhilgarg]

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.

[#3]

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

[#4]

thank you

[#5]

Please close the thread If you get it what you wanted by marking it correct .

Thanks

[#6]

thanks for the reply.

[#7]

Its alright.

You can mark it correct as correct answer.

If you feel like..

thanks

[#8]

Hello S Shrivas,

Below expr :

• Sum({$<MonthID = {“>=$(=Max(MonthID) – 11)<=$(=Max(MonthID))”},
  Year = ,
  Quarter = ,
  Month = >} Sales)

May I know that, here Year =, Quarter =, Month =,. what we would get here.

Thanks,

Ananth

[#9]

hi,

This is for sales for rolling 12 months.

and by doing

Year = ,

Quarter = ,

Month =

we are bypassing it,from the expression.

thanks

sudhanshu shrivas

[#10]

Means, these will select all Year, Quarter and Months right?