Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Help using cross tables? Or problems with data retrieval from Excel

Hey,

I have a spreadsheet containing forecasts that I want to compare against sales by month.

Spreadsheet.jpg

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.

Here is a screenshot of the script

Edit Script.jpg

And finally this is how the expressions look.

Expressions.jpg

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.

Result.jpg

Can anyone shed some light on this situation?

5 Replies
Not applicable
Author

Hi,

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?

Regards,

Kiran Rokkam.

Not applicable
Author

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.

Not applicable
Author

Hi,

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.

Regards,

Kiran rokkam.

Not applicable
Author

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.

Thanks for all your help.

Not applicable
Author

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.

Enclosed document will have some examples for your reference.

Regards,

Kiran Rokkam.