Previous Quarter all months

[MK9885]

Hi,

I have a requirement where if I select Year = 2017, Month = Dec, I should see Data for Year = 2017, Month = July, Aug & Sep

And if I select Month as Sep, I should see data for Jun

And if I select Month as Aug, I should see Apr & May

In short whatever month I select I should see data starting from previous 3 months to whatever months left in that quarter.

I used

sum({<Num_Month={'$(=Max(Num_Month)-3)'},Month  > }Amount)

This gives me 3rd month of any month I select

and if I use sum({<Num_Month={'$(=Max(Num_Month)-3)'},Month,Quarter = p(Quarter)  > }Amount) I get 0

using p (Quarter) doesn;t help with Num_Month - 3...

Any suggestions?

stalwar1‌

Thanks

[sunny_talwar]

When Dec is selected (Last Month of 4th Quarter), you want to see July, August, September. But, when Sep is selected (Last Month of 3rd Quarter), you want to see just June? What is the logic here?

And if I select Month as Sep, I should see data for Jun

[MK9885]

The logic is

If last month of any quarter is selected, I should see data for all the PRIOR months in that selected quarter & previous quarter.

Ex:

If Dec is selected, I should see Jul, Aug & Sep and Oct, Nov & Dec

But if I select Nov month I should see only Oct, Nov & from prior quarter I should see only Jul & Aug.

So if 3rd month is selected I should see all the prior months from selected Quarter and previous quarter...

if 2nd month is selected, I should see selected month and prior month in selected quarter & prior quarter

if 1st month is selected then I should see 1st month of selected quarter and 1st month of prior quarter.

I hope I could explain it... I know it is quite stupid

For one of the expressions I used

fabs(num(sum({ <

[ FLAG]={1},   

Num_Month = {  '$(=Max(Num_Month) -2)'} , Quarter= p(Quarter), Month >+<

[ FLAG]={1},   

Num_Month = {  '$(=Max(Num_Month) -1)'} , Quarter= p(Quarter), Month

>+<

[ FLAG]={1},   Num_Month = {  '$(=Max(Num_Month)  )'} , Quarter= p(Quarter), Month

>}Amount ),'#.##0;#.##0'))

The above expressions works fine for selected quarter

But now I want to show same for previous quarter & also if Jan is selected, I should see Oct data from 2016 same way if Feb is selected I should see Oct & Nov from 2016 (which is done)

Everything is working fine but when I start selecting Feb, I Just see Num_Month-3 data, a single months data but not the prior months in that quarter.

Thanks.

[sunny_talwar]

Again, I understand what you mean... but what confusing me is this

"And if I select Month as Sep, I should see data for Jun"

Do you expect to see only Jun when Sep is selected? or do you expect to see Apr, May, & Jun?

[MK9885]

sorry if I select Sep, I should see data for July, Aug & Sep in one Column (This is completed as I have given the expression above but you can correct me if I'm wrong.)

and

I should see Apr, May & Jun data in another column  (Here I see only Jun data)

Plus for prior month quarter I've to hard code Jan, Feb & Mar to get previous year month quarter.. the actual expression is really complex and maybe not good

Look below for original messed up expression, which works for only Jan, Feb & Mar for previous quarter month

=if(Month='Jan',

sum({<Num_Month={'$(=Max(Num_Month)+9)'},Month,[ FLAG]={1},Year={'$(=Max(Year)-1)'}  > }Amount_$(vDisplayCurrency)_AC)  ,

if(Month='Feb',

sum({<Num_Month={'$(=Max(Num_Month)+9)'},Month, FLAG]={1},Year={'$(=Max(Year)-1)'}>+< Quarter=p(Quarter),Month,

[ FLAG]={1},Year={'$(=Max(Year)-1)'},

[Quarter Number]={'$(=Max([Quarter Number])-1)'}

>+<

Num_Month={'$(=Max(Num_Month)+8)'},Month,[ FLAG]={1},Year={'$(=Max(Year)-1)'}>+< Quarter=p(Quarter),Month,

[ FLAG]={1},Year={'$(=Max(Year)-1)'},

[Quarter Number]={'$(=Max([Quarter Number])-1)'}> }Amount ) ,

if(Month='Mar',

sum({<Num_Month={'$(=Max(Num_Month)+9)'},Month,[ FLAG]={1},Year={'$(=Max(Year)-1)'}

>+<

Num_Month={'$(=Max(Num_Month)+8)'},Month,[ FLAG]={1},Year={'$(=Max(Year)-1)'}

>+<

Num_Month={'$(=Max(Num_Month)+7)'},Month, FLAG]={1},Year={'$(=Max(Year)-1)'} > }Amount ) ,

sum({<Num_Month={'$(=Max(Num_Month)-3)'},Month,[ FLAG]={1}  > }Amount )))))

[MK9885]

stalwar1‌

I made it work but sadly I had to hard code the missing months which made my expression almost 20 lines.

there are only 2 sheets and not much data so I guess it should not impact the app load.

BUT, if you can give me an optimized way to write it, it would be great!

Thanks.