Field not displaying

[qlikofba]

I am trying to print the previous 13 month ends in a pivot table across the top.

The dimensions i am using are:

     if(WildMatch(TrendGroupDesc,'*Retail CD*'),plan_desc_nm)

and

     PrevMonthEnd

my expression is:

     sum({$<Year=,Month=,Week=,Date=,%Date=,prod_dt=,[Month Year]=,MonthNo=,YearMonthNo=,YearMonthSeq={"<=$(=max(YearMonthSeq)) >=$(=max(YearMonthSeq)-12)"},MonthEndFlag={1}>} mtd_avg_bal_amt)

I am not sure why, but the dates are randomly skipping a month, depending on how I edit the second dimension. The data is correct for the remaining months, but I cannot figure out why all previous 13 month end dates aren't appearing.

When I use a list box on the 'PrevMonthEnd' field, all the month ends show including the one that is missing from the table

[sunny_talwar]

Is there a sample you can share showing the issue? It is difficult to troubleshoot the issue without looking at it

[swuehl]

What do you mean with 'depending on how I edit the second dimension'? I don't understand that part.

Besides this, have you disabled 'suppress zero values' on presentation tab?

And have you checked that you have data (expression results) for all combinations of the dimension values you expect to show up?

[qlikofba]

Sorry, forgot the editing the second dimension part.

I had It originally checked off, but just unchecked the suppress zero values, and it is still missing.

I created another pivot table with dimensions of PrevMonthEnd, TrendGroupDesc, and plan_desc_nm with the same expression.  There still is the same month missing.

When i change the expression to sum(mtd_avg_bal_amt) the month then shows, but shows the most recent two month ends have number that are way off.

[swuehl]

I think it would be more than helpful if we could have a look at your data model and the complete setting.

Could you upload a small sample QVW that demonstrates your issue? You don't need to post the real data, just some sample records (that still show your issue).

[qlikofba]

The document is very complex and I am unsure how to put sample records into my document.

If it would help i can post the snippets of code where the fields are being pulled from.

[Gysbert_Wassenaar]

Perhaps this document helps: Preparing examples for Upload - Reduction and Data Scrambling

[qlikofba]

I think I found out the issue but still need a bit of help resolving it.  The months that are missing are those that have month ends on a Sunday(which we carry no data).  The months are May2015, and Janurary 2016.

I created a text object with this field

= if(%Date=Date#(MonthEnd(%Date)),1,0) (which is stored as MonthEndFlag) in the data Model.

When I select 1/30/2016, the month end flag = 0.  I need to incorporate the logic to say is the date of the month end is a Sunday, get the previous day.

Once again here are the fields from the data model

date(%Date-day(%Date)-(if(WeekDay(%Date-day(%Date))='Sun',1,0))) as PrevMonthEnd,

     if(%Date=Date#(MonthEnd(%Date)),1,0) as MonthEndFlag

I would prefer to make a new field for the month end flag with the Sunday logic in it.

[swuehl]

If

date(%Date-day(%Date)-(if(WeekDay(%Date-day(%Date))='Sun',1,0))) as PrevMonthEnd,

gives you the right prev month end (and this is already checking for Sundays, then

If(%Date = (floor(MonthEnd(%Date))- if(WeekDay(%Date)='Sun',1,0) ),1,0 ) as MonthEndFlagNew

should work as well.