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

# 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

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

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

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

Thanks

• ###### Re: Set analysis

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

• ###### Re: Set analysis

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

• ###### Re: Set analysis

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

• ###### 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

Its alright.

You can mark it correct as correct answer.

If you feel like..

thanks

• ###### Re: Set analysis

Hi Guys,