Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
qlikofba
New Contributor II

Field not displaying

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

8 Replies

Re: Field not displaying

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

MVP
MVP

Re: Field not displaying

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
New Contributor II

Re: Field not displaying

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.

MVP
MVP

Re: Field not displaying

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
New Contributor II

Re: Field not displaying

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

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


talk is cheap, supply exceeds demand
qlikofba
New Contributor II

Re: Field not displaying

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.

MVP
MVP

Re: Field not displaying

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.



Community Browser