Date flags not working with linktable

[tseebach]

Hi,

I have a textfield were I want to show the sales on a year to date basis. And I've built the structure around a linktable, where multiple dates are put into a master calender associated with one general date field in the linktable.

The formula is:

= sum(Sales*Calender.YTD_TY)

But this calculates wrong, it takes the sum of Sales and the sum of Calender.YTD_TY and multiplies them. Eg 273.000 * 93 since there are 93 entries that match in that period.

If I add an extra date fields in the same tables as Sales, and calculate the flag there it works as it should. And when doing selections on the date and just calculate Sum(Sales) it also returns the correct values. And even sum(Sales)*Calender.YTD_TY returns correctly. But I would like to use {1} to make the fields value fixed, so those other method are not a option.

Has anyone experienced something similar? Or does anyone have a god idea on how to solve this?

[Oleg_Troyansky]

It sounds like you might have a problem with the level of multiplicity. Can you post a sample, or at least an image of your table viewer?

When all the links are accurate and all the values are distinct, it shouldn't happen...

The first thing to check into is how many duplicate rows do you have in your LinkTable. If you don't ensure distinct values in your link table, this is the natural outcome.

cheers,

Oleg

[tseebach]

Hi Oleg,

Good idea checking for duplicate rows, sadly there weren't any other than what I was expecting.

QlikTech won't be happy if I post the solution since its part of the combo cert. So I guess the table viewer has to do and a reduced version of the file, I've removed all except calendar and Treat and txBilling table, the error is the same, see the textfields on the right. Its the value of Treat.Sales linked via KEY_INVOICE to MasterCalender YTD_TY (or any other flag) that has the issue.

I appriciate your time 🙂

[johnw]

I wouldn't do the multiplication with the flag in the first place. I believe it forces QlikView to look at ALL rows of sales, including those where the flag is 0, though perhaps I'm wrong and it's optimized enough to skip them. But I'm pretty certain that if you use set analysis to only select rows where the flag is 1, QlikView will only have to examine those rows, which should be faster. On top of that, it returns the right answer, which is always a good thing.

='YTD_TY '&num(round(sum({1<Calender.YTD_TY={1}>}Treat.Sales)),'#.###.###')

='YTD_LY '&num(round(sum({1<Calender.YTD_LY={1}>}Treat.Sales)),'#.###.###')

Now, you'll get different results if you plug that directly into your QVW, because you've made some selections, and the {1} tells it to ignore them. But I assume that's what you wanted since that's what you used in your own expression. If you clear all the selections, that will make it match.

[tseebach]

Hi John,

You're stepping into a rather big discussion. When 8.5 was released I was jumping because I finally saw an end to the horrendous dataflags system that has been best practice for a long time. Its a really ugly method, and any programmer coming from any structured language would be getting goosebumps using it.

But set analysis is not as easy to use as one would like, and its hard to reuse. Dateflags are super easy to use, and very flexible when first set up.

If we where to come up with an as easy to use method for set analylsis this might change. I'm thinking of a include file with some variables, that can be put into the expression fields where needed.

Some of the consultants at QlikTech are with us on this, and some still find Dateflags easier. Its a real method divide, we should do a speed test some day 😉

[johnw]

Yep, it's definitely a big discussion. I haven't had enough need of things like "last year year to date" to make and reuse variables, but it seems like a reasonable approach.

I don't use date flags myself. My concern is the lack of flexibility. What if, when comparing this year to date with last year to date, I want to select which date to compare through? The flag is rigid, while it is a simple matter to write expressions based on max(Date) instead of on today(), or even more complicated expressions still.

But if you're not looking for maximum flexibility, the more you can do at load time, the less time you'll spend at reporting time, and the happier your users will be. As an example, I may need to revisit one of my applications that uses a lot of set analysis for date manipulation - it is flexible, but one of the tabs takes many seconds to come up. The users would probably rather have less flexibility but higher performance for the normal cases (month to date and year to date analysis). I might be best off creating flags for this purpose, then using set analysis on the flags.

[Oleg_Troyansky]

Torbent,

I didn't have time to get into all the details of your code, but overall it looks to me a bit over-engineered. I doubt if the result of the "Combo test" should be this complex...

My hunch is that you use the concept of the LinkTable a bit incorrectly. Usually, the LinkTable would include one (or more) concatenated keys, along with the separate key components. In your case, 3 tables are linked by KEY_INVOICE, which is not concatenated . There are approx. 55K of distinct values for KEY_INVOICE, yet approx. 128K of rows in the LinkTable and in the "Treat" table (and the two counts are not exactly the same) - this is precisely the level of multiplicity I meant. Somewhere along the line the Sales amount is being multiplied because more than 1 row corresponds to it in the Link Table.

Revise your tables structure and think along the lines of simplification.

good luck!

Oleg