text: Help using cross tables? Or problems with data ret... - Qlik Community
  Help using cross tables? Or problems with data ret...
  Help using cross tables? Or problems with data retrieval from Excel
  I have a spreadsheet containing forecasts that I want to compare against sales by month.
  I've then used a cross table to bring it in as that's what I was told I needed to do for spreadsheets of this nature.
  And finally this is how the expressions look.
  My problem is that some of the item codes are missing the forecast data, while alot of the other products areshowing the right amount.
  Eg. Look at FAA and then look at FAA on the top picture of the spreadsheet. There's nothing different about it, but it just won't bring the forecast data through, yet some other items look to work perfectly fine.
  Re: Help using cross tables? Or problems with data retrieval from Excel
  Propably you already tried this:
  1. To ensure that forecast data is correct disable first expression and validate.
  2. If the numbers are incorrect, some issues with the data model (like joins can create duplicates).
  Can you share the table viewer?
  ‎06-25-2012 12:09 AM
  Alright, a few more things which might help find the answer to my problem.
  When the 01/05/2012 and 01/06/2012 forecasts are identical, the table will show the figures, although they are really incosistent and some item codes are missing and some forecasts are missing for one of the months etc.
  Now when I change the figures for 01/06/2012 to be different from May's, then the data disappears and the table shows nothing.
  I have attached a screenshot of the table viewer.
  Table viewer.jpg
  ‎06-25-2012 12:20 AM
  From the table viewer the problem seems to be clear. You have forecast value for each item and FCDate. In the pivot table you used MthNum which is given from a different table with item code and monthnum based join.
  Hence for each item, irrespective of the MthNum there would be a multiple FC if the values are different. To see your problem clearly please check out with the expression of FC as Sum(FC). If your cardinality is correct you would get the same exact values.
  Solution: Concatenate forecasts table to facts table with FCDate as Mth.
  Yeah, I use MthNumber because when I have the sales quantity in the table it uses that field to display.
  My next question is,... How do I concatenate the forecasts table to facts table with FCDate as Mth?
  Sorry, Im new to Qlikview and my SQL code skills are not very strong.
  Basically you need to convert FCDate to Mth with the same forma and then concatenate. Ofcourse, you need to have the values in Mth Table as well.