topic Re: Field not displaying in QlikView

Field not displaying

qlikofba — Fri, 01 Apr 2016 15:03:24 GMT

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

Re: Field not displaying

sunny_talwar — Fri, 01 Apr 2016 22:24:01 GMT

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

Re: Field not displaying

swuehl — Fri, 01 Apr 2016 22:36:52 GMT

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?

Re: Field not displaying

qlikofba — Mon, 04 Apr 2016 12:22:06 GMT

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.

Re: Field not displaying

swuehl — Mon, 04 Apr 2016 12:50:40 GMT

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).

Re: Field not displaying

qlikofba — Mon, 04 Apr 2016 13:02:46 GMT

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.

Re: Field not displaying

Gysbert_Wassenaar — Mon, 04 Apr 2016 13:51:30 GMT

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

Re: Field not displaying

qlikofba — Tue, 05 Apr 2016 13:47:02 GMT

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.

Re: Field not displaying

swuehl — Tue, 05 Apr 2016 17:09:37 GMT

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.