Show the Last 6 Months Based on Current Fiscal Month

[Anonymous]

Hello!  I would appreciate if someone could look into my formula...

I have a  FiscalYYYYMM  dimensional field (like   201701 ,201612,201611) in text  format .  What I would like to do is to show only the last 6 months based on current  Fiscal Month 201701...  Thus, for this Fiscal Year 201701,    I would expect to show only the sum from  201608 - 201701  and for next month, it should  show from  201609 - 201702...

With the formula below, here's  what happened

sum({<Company={'A'},[Fiscal YYYYMM]={">=$(=max([Fiscal YYYYMM])-5)<=$(=Max([Fiscal YYYYMM]))"}>} Qty)

1..   If I click in Fiscal Year 2016,  it show's   only the last 6 months of FY 2016  201607 - 201612  (and same with FY2015 and below)

2.  If I click on FY 2017, it's showing nothing.  Is it because we are sill on the 1st month of FY2017?

3.  Is there any way to add in the formula to ignore the selection on FY, FQ and FM?....so my expected output  2016608 -201701 would show up.   I tried using the formula below but it didn't work.

      sum({<Company={'A'},FY=,FQ=FM=,[Fiscal YYYYMM]={">=$(=max([Fiscal YYYYMM])-5)<=$(=Max([Fiscal YYYYMM]))"}>} Qty)

Thank you in advance.

[swuehl]

Your YYYYMM calculations are not considering year change correctly (201701-5 results in 201696, not 201608).

Have a look at

The Magic of Set Analysis - Point In Time Reporting • Blog • AfterSync

[swuehl]

And maybe also have a look into

Cyclic or Sequential?

Relative Calendar Fields

[sunny_talwar]

Can you share a sample of what you have and we might be able to offer a better advice. You mentioned FiscalYYYYMM is a text field? May be it would help to read this as a date field within QlikView.

Why don’t my dates work?

Get the Dates Right

This combined with ignoring other date time field might help you resolve your issue. But, again unless we have something to look at, this would be a waiting game for us.

Best,

Sunny

[Anil_Babu_Samineni]

Have you tried using addmonths

sum({<Company={'A'},FY=,FQ=FM=,[Fiscal YYYYMM]={">=$(=addmonths(max([Fiscal YYYYMM]),-5)<=$(=Max([Fiscal YYYYMM]))"}>} Qty)