Numbers in Excel_export don't quite match QlikView ...
Hi,
I have the following very confusing issue in a QlikView_app:
There is this formula which I've posted here a few times in the past to count orders. I am using two Flagfields now and in QlikView, the figures are correct - there are two objects in QlikView, a pivot_table with only the dimension "month" and a detail table underneath which has a whole bunch of dimensions.
Now, I have a column with the same formula in both tables. In the below detail table, when I tick the radiobutton "formula across all rows", the figure is correct - when I tick "sum of y values", it is of course much higher, there are many more rows - there is 1 or 0 in every row in that column.
The figures must match between the pivot_table above and the detail table below. To check that this is so, I have previously marked all the dimensions that must be deactivated so that the "sum of y values" matches the figure in the other table.
The funny thing is, in some months - not in all and only under specific circumstances - I get wrong figures by only one or two orders - less orders in the detail table than in the pivot_table above.
- When I deactivate all the marked dimensions in QlikView, the figures match in QlikView - always;
- When I deactivate those dimensions and export the table to Excel and count there (by first sorting and eliminating duplicates), figures match - always;
- When I activate all the dimensions again and export to Excel and count (the same way), the figures do not match - only in certain months.
Now I am one step further: I know what exactly the error is - but I don't know why 😉 (this is where I hope someone can help me)
The error is, when I leave the dimensions "Validity date" and "Creation date" active (those two are in one of the flagfields used, I want to count only instances where they are in the same month),
then the two orders I am missing in one specific month are correctly identified as irrelevant - they both have a Creation_date in April.
<=> When I deactivate those dimensions, they are incorrectly identified as being relevant ... I have yet to check the elements of the other flagfield.
It makes me wonder whether the visualization_objects in QlikView are correct - but that is another story.
I cannot make head or tail of this behaviour - I use a flagfield that is a "native" field in the data - so that shouldn't be dependent on the dimensions of my chart? I also have to check if that same phenomenon holds true in the other months which seemed until now to show correct numbers ...
Hmm ... okay. I have discussed the issue with colleagues and it appears that an order containing lines (presumably the very first) with a CreationDate in a previous month should not be counted because they have presumably been counted in that previous month. So it's only a question of sorting now. If I was to export the entire detail table with all its dimensions to Excel, sort on DocNo AND position (instead of DocNo only), then eliminate Duplicates and then sum up my "0/1" field (being 1 only for relevant orders with a CreationDate in the selected month), those orders would end up with the first line (the only one remaining) having a CreationDate in a previous month, it would thus not be counted - voila.
<=> The implication, however, is that in the month of May 2018, the 346 orders I got when "manually" counting in Excel would have been correct, not the 348 I get in QlikView. That leads to another problem.
My COUNT(DISTINCT()) finds several positions belonging to this order which do have a CreationDate in May and just like it's supposed to do, it counts one of them - but it shouldn't because the order also has one (or more) position(s) with a CreationDate in a previous month and there's no flagfield for that.
=> So now I need (either some rather complex set_analysis or) another flagfield which means I must do some aggregating or else in the script comparing the nr. of positions per order with the nr. of positions with a CreationDate in the month of the Incoming_Date.
