I seem to be having a problem creating expressions on a fairly complex (to me) set analysis. Here is the basic scenario:
We stock parts (PARTNO) both on site and off site (SITE). Our historical data goes back 3+ years, and is categorized by ONE_YEAR, TWO_YEAR, and THREE_YEAR. A ‘delivery’ time is calculated based on the number of days that it takes for a part to be delivered from the time it was ordered (ORDNO, [ORDER DATE] and [END DATE]) from either ON-SITE or OFF-SITE. This is calculated as 1 day if the part is delivered the same day, 2 days if the part is delivered the day after the order, and so forth – TRR_DAYS shows this number.
What we need QlikView to do is calculate a 90th percentile of the TRR_DAYS for a given PARTNO, round up to the next whole number, and then select either the matching TRR_DAYS or if a matching TRR_DAYS doesn’t exist, the next larger one that does (limited to 99).
The results that we get are all over the map. It comes down to basically 4 problems:
- The set analysis that we have set up appears to work generally, but there are times where the results (TRR_OFF and TRR_ON) seem to change based on the number of PARTNOs selected. As an example, select the “5 Items” bookmark and check the compare the numbers for PARTNOs 007832383 and 011397177 with the “25 Items” bookmark.
- QlikView also doesn’t seem to have an expression function that will let us reliably choose the next higher number in the range based on a given number.
- Another odd thing that happens is when I select a single PARTNO, the calculated numbers change (although I wouldn’t think they should if Set Analysis is working correctly.
- Ideally we wouldn’t have to have the SITE and ONE_YEAR as dimensions because we also want have some expressions using three year data on the same page.
I have included a stripped down QVW named AppToSend.qvw where the bookmark called “25 Items” matches the errors highlighted with red text on the Main tab of the attached Excel document (AnswersForPosting.xls). The Excel spreadsheet also demonstrates how we calculate the correct answer, and what the correct answer should be. I included tabs for the first couple PARTNOs, both ON-SITE and OFF-SITE (correct or incorrect) as well as for every other calculation that is wrong. Any correct calculation over 99 (as shown in Correct OFF and Correct ON columns) can be ignored as we will wrap working code with an IF statement to check for values over 99 and treat accordingly. Incorrect calculations that come up with an answer of 99 or greater need to calculate the correct number.
Truly appreciate any help/ideas you can give me.
