Two different date_fields

[datanibbler]

Hi,

this is actually a follow-up on an earlier post by myself, but in some way it's new, so I'll make it a new thread to keep things nice and orderly:

- The visualization I'm dealing with is the nr. of orders (eCommerce orders, that is) in a given month.

- The issue is, every order is connected to the regular calendar via a date PLUS it has a creationDate which may not be identical - an order can be created "in advance" with a later validity_date.´

- What I wanted there was to show only the tickets for a given month (say January 2018) which were created in January 2018, not those which had been created earlier.

- As long as exactly one month is selected, that works fine now. I have included another generated field, the >> CreationYear << and >> CreationMonth << into a set_expression to sum up the nr. of orders.

The issue is, when no month is selected, of course this set_expression cannot do its work. So, if I select 2018 now, figures for Jan through March are displayed, but in March I see tickets that were created in either of those three months. When I select March, of course it's fine, but there is a significant difference - which will get "worse" for later months.

=> Until now, I cannot think of any way that could be solved in one chart - I think I'd actually need a second dimension "CreationMonth" whereby I'd have to specify that in March (I'd have three "sub-dimensions" in the "regular calendar month" of March), I want only the third one (subdimension March) and in June (there'd be six sub-dimensions), I want only the sub-dimension of June ... I cannot think of any way that would work.

As I see it, I'd need twelve individual small charts, one for each month, where I can use the set_expression I have, each with a visibility_condition.

Can any of you think of another solution?

We'll have a meeting on this and talk about how to do this - the fact being, the figures are not wrong either way, just different ... I'd just like to have one or two possibilities in my hat.

Thanks a lot!

Best regards,

DataNibbler

[datanibbler]

Hi Jonathan,

many thanks! I'm afraid I haven't fully understood from your post why this expression cannot work.

You're right about what I'm trying to do, though - the goal is to display for every month only the orders that were created in that month.

The DocumentNumber is in a linktable, so I have different fields to be used as the date - the date is the link to a calendar - but there is another date, the CreationDate, which is unrelated to the calendar.

Bearing in mind that using flags in the script which are calculated only once is usually more efficient than having complex expressions on the GUI that are executed multiple times, I will try whether I can create such a flag - then I could also set a flag for the other elements of the set_expression that I currently have and have just a very simple set_expression on the GUI. Or maybe I can even build a flag to only be 1 once for every document number and then do a sum() instead.

Let's see ...

[datanibbler]

Many thanks Jonathan!

The solution now was so much easier, it makes me wonder why I didn't think of it - using flags in the script instead of complex set_analysis is actually something I like to do wherever possible ... well, I still need some set_analysis, but only to specify for my flagfields to have the value 1. Instead of making just one flagfield for the entire formula, I created two to keep things more flexible - that way, they can be used for something else if the need arises.

Best regards,

DataNibbler

[datanibbler]

Hi,

I realize again - as a number of times before - that I could use some real training in set_analysis. I'm not so proficient in that, I never had any training at all ... on the other hand, having complex set_expressions in charts (on the GUI) is generally not recommended from a performance point_of_view. It is very flexible and would allow anyone who has mastered this tool to get a lot of information out of a given dataset without editing the datamodel, thus in very short time (if you know how, writing the formula is supposedly quicker than editing the datamodel). Well, okay. I'll see if I can get some training.

One last thing - I think I read in some book on QlikView that instead of a COUNT(DISTINCT()) of something, it would c.p. be a lot more performant to use a sum() because sum() works on a deeper level close to CPU_level whereas COUNT(DISTINCT()) is executed on Software level - is that right? I'll try to find this in my books or here in the community, then I could insert that into my own "best practices" catalog.

Best regards,

DataNibbler

P.S.: Hmm - there is a document here from HIC saying that nowadays that is no more true and COUNT(DISTINCT()) is by no means slower than sum(). So I will focus on other possibilities of speeding up the GUI of our reports - I guess there are plenty, starting from the fact that many straight tables in our repórts have more than a dozen dimensions ...

[jonathandienst]

>>CreationDate = {$(=Makedate(Year,Month,'01'))}

>>I'm afraid I haven't fully understood from your post why this expression cannot work.

Set expressions are not evaluated on a row by row basis, they are evaluated once per chart. So the context of the set expression is actually the same as in a text box. in this context, fields like Year and Month have more than one possible value. When QV encounters more than one possible value, the expression cannot be evaluated so it returns null().

As is so often the case, HIC explains the issue well.

Use Aggregation Functions!

[datanibbler]

Thanks Jonathan!

I will read that asap. I think I understand the issue now.