11 Replies Latest reply: Nov 25, 2016 7:55 AM by Nithyakala R

Previous Month for Current Year

I wanted to show 2 line chart

1.current year current month

2.current year previous month.

Dimension:Month

My current Year current month expression is

sum({<type={'Order'},FiscalYear={\$(=max(FiscalYear))}>} Sales) (works fine)

My current Year previous month expression is

=(above(Sum({<type={'Order'},FiscalYear={\$(=max(FiscalYear))}>}Sales ))) (issue)

If I use above expression till may (right to left) I am getting precise value ,however  apr is null,

Can any one help me?

• Re: Previous Month for Current Year

I would recommend reading this :Calculating rolling n-period totals, averages or other aggregations  , which would actually help you learn the trick of using aggr() along with set analysis to deal with such issues.

• Re: Previous Month for Current Year

Based on this FiscalYear={\$(=max(FiscalYear))} your Fiscal year is ended in May i guess. so it wont get the Apr.

Do you have a FiscalYear_Month field ---- 2014-04?? or in number format like 40567??

prefer to have number format...makes life easier in set analysis...if u dont have in number format create one in your script...say DateKey

create a variable called "vPriorRolling12"

='>='&floor(addmonths(max(DateKey),-13))&'<='&floor(addmonths(max(DateKey),-1))

sum({<type={'Order'},DateKey={'\$(vPriorRolling12)'}>} Sales)

if this doesn't work, share your QVF and i will take a look.

• Re: Previous Month for Current Year

First Thank you

My Fiscal Year starts from apr to end by mar

Let  me explain clearly

In Line chart

current month apr(2015) ->mar(2014) previous month ,

current month may(2015) ->apr(2014) previous month ,

current month jun(2015) ->may(2014) previous month

• Re: Previous Month for Current Year

gwassenaar Can you please help me?

• Re: Previous Month for Current Year

Could you upload your sample qvw?

• Re: Previous Month for Current Year

how to get before six month data using expression

• Re: Previous Month for Current Year

I don't understand what you want to show in your line chart. If you use month as dimension then the value of the current month is one value so you get a point in the chart, not a line. The same for the previous month.

• Re: Previous Month for Current Year

Sorry ,not current month

Actually I need values for all fiscal months for current fiscal year in one line chart

Another line chart   is previous months of same year  value should be shown to corresponding  current year line  chart.

Totally two line chart .

Output should be like this

please probe more if you don't understand?

• Re: Previous Month for Current Year

Perhaps like this:

sum({<FiscalYear={\$(=max(FiscalYear))}>} aggr(above(total sum({<type={'Order'}>} Sales)),FiscalYear,Month))

• Re: Previous Month for Current Year

Thank you it's working fine  for Month.

I have set up drill down for Dimension  by Month-> DAY.

For Month it works fine.

IF I drill down to Day all values are  '0's.

Any solution please?

• Re: Previous Month for Current Year

i want to show previous rating and current rating in chart by resource name? i have start date and end date for that ratings

which is for 6 months