8 Replies Latest reply: Apr 5, 2016 1:09 PM by Stefan Wühl RSS

    Field not displaying

    ant graz

      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

          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
            Stefan Wühl

            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
                ant graz

                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
                    Stefan Wühl

                    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
                        ant graz

                        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
                          ant graz

                          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
                              Stefan Wühl

                              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.