Previous Month for Current Year

[scotly-victor]

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?

[tresesco]

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

[phaneendra_kunc]

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.

[scotly-victor]

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

[scotly-victor]

gwassenaar‌ Can you please help me?

[tresesco]

Could you upload your sample qvw?

[Gysbert_Wassenaar]

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.

[scotly-victor]

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?

[Gysbert_Wassenaar]

Perhaps like this:

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

[scotly-victor]

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?