topic Re: Two different date_fields in QlikView

Two different date_fields

datanibbler — Mon, 30 Apr 2018 10:22:58 GMT

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

Re: Two different date_fields

kamal_sanguri — Mon, 30 Apr 2018 10:32:54 GMT

May be you want to create different date fields (make them island) and then use the values selected in those filter in set exression.

Re: Two different date_fields

datanibbler — Mon, 30 Apr 2018 11:11:49 GMT

Hi Kamal,

I don't quite understand: I have a set_expression -. the issue is, in a set_expression, I have to use something like >> P(Month) << (selectable values as dictated by user selection) or >> max(month) << - but I'd need something to make the limitation on my CreationMonth dynamic - actually several different limitations would have to be active at the same time, one for every month shown (when none is selected).

Maybe I could do something with the AGGR() function? That one "simulates" a dimension, no?

I'm not proficient enough to tell by heart whether that might be feasible, I'll give it a think.

Best regards,

DataNibbler

Re: Two different date_fields

kamal_sanguri — Mon, 30 Apr 2018 12:47:38 GMT

What I could understand is the problem may be with the selection.. Pls see the attached snippet to clearly understand what I am saying..

I have created another table with just the Date. I have renamed it just to avoid making any association. Although these two are separate but you can use the value selected in the [Creation Date] in your set expression. Something like this

Sum({$<Date = {'<=[Creation Date]'}>}Col1)

Certainly you have to modify it per your needs. I assume you may want to create two such tables and may be some more columns like month or year alongside date aswell.

Regards,

-Kamal

Re: Two different date_fields

datanibbler — Mon, 30 Apr 2018 14:26:20 GMT

Hmm ... sorry, I still don't see how that would be applicable to my scenario:

- My dimension is month

- I have a barchart

For a set_expression to work (afaIk) I need something to compare my field (which would be CreationMonth) to - I cannot make that field match "whatever dimension_value (month) that order is for", can I?

But I just remember something - when working for another company, I created a "bogus barchart" which looked like a barchart, but in fact it displayed, on a bogus dimension, two completely unrelated, constructed figures - one for the last month and one for the last week. That might be a way, I'd only have to make that appear if the user selects a year, but no month.

I'll try.

Thanks a lot!

No, sorry, that was bogus - the chart I want to display these figures is a table.

However, the >> Col(1) << functionality (building one expression on top of another) is a possibility - but I'd have to turn it around: I'd need two columns for every month (dimension) - the second having a set_expression for "CreationMonth = [current_month]

Well - I think I could somehow do it with 12 expressions, each one being displayed for just one dimension_value - then I'd just have to do some hiding and display one "summary_expression" with the 12, to look as if it was just one ...

Well, it is doable ...

No - it is a pivot_table which does not have quite the same possibilities as a straight_table ... but I can edit the text_colour of that expression, making it black (visible) for one particular month and white (invisible) for all others. I don't yet know how to finally collapse all those into one row, but it is a first step ...

Re: Two different date_fields

datanibbler — Thu, 03 May 2018 11:14:28 GMT

Hmm ... was there a general issue with the webSite yesterday or was that just me? I could not log on here, I always got a message about an SSO error - all day ...

Well, I can display the first day of a month in that chart (for the dimension "month") - so I guess I could maybe incorporate that into my set_expression - I only have to specify that >>CreationDate >= FirstDayOfMonth << - no point in specifying the last day of the month since an order cannot be valid before its CreationDate ... should not ...

I'm curious to see if that works ...

If not, I could create another pivot_table with a bogus_dimension (inline) and twelve expressions, with each one being displayed for only one dimension_value - that's similar to something I did for an earlier employer - there I displayed two completely unrelated figures in what looked like a columnchart ... Then I'd just have to adapt the size of that "pivot_table" so that it positions seamlessly underneath another one.

Well, I guess I'll come up with some possibility ...

Re: Two different date_fields

datanibbler — Fri, 04 May 2018 07:58:52 GMT

Hi,

as expected, it doesn't work yet - the issue is, I don't know whether it is because I cannot use the dimension in a set_expression or whether it is because I'm doing it wrong - I am not very proficient in the use of set_analysis. Maybe I'll get some training sometime ...

So I have a straight_table (it's going to be a pivot later, but I just use a straight table for testing) with the dimension "Month" (from my calendar). I can display the first day of the month. My idea was to use this in my set_expression to specify that >> the CreationDate (of an order) must be equal to or greater than the first day of the month".

My code currently looks like this:

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>><<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

count(DISTINCT{<Year={$(=max(Year))}, OrderSource={'SHOP'},ProfitCenter={'P10 - Spare Parts'}, CreationYear={$(=max(Year))}, CreationDate = {$(=Makedate(Year,Month,'01'))}>} DocumentNumber)

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>><<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

The expression without that last part for the >> CreationDate << works fine. I have a year (calendar) selected, but no month.

Can somebody tell me whether there is a syntax error in there?

Thanks a lot!

Best regards,

DataNibbler

P.S.: Of course this would specify for the CreationDate to be equal to the first day of the month - but currently even this doesn't work.

Re: Two different date_fields

datanibbler — Fri, 04 May 2018 08:04:50 GMT

Maybe I need an extra field "first day of month" in my calendar to use in that set_expression?

Worth a shot ...

Re: Two different date_fields

jonathandienst — Fri, 04 May 2018 08:32:18 GMT

This part won't work

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

because Year and Month have no aggregation function. It looks like you are attempting a line by line comparison for creation dates in the same month as the document validity date. it may be possible to do this in a set expression, but I am not sure what date fields you have.

Possibly something like

Count(DISTINCT

{<Year={$(=Max(Year))},

OrderSource={'SHOP'},

ProfitCenter={'P10 - Spare Parts'},

DocumentNumber = {"$(=InMonth(ValidityDate, CreationDate, 0))"}

>} DocumentNumber)

The alternative is a sum(if()) or create a flag field in your load script to flag documents with creation date and validity date in the same month.

Re: Two different date_fields

datanibbler — Fri, 04 May 2018 08:35:09 GMT

Okay - so I can specify >> CreationDate = P(FirstDayOfMonth) << - that is a step. The figures are correct then - but for the month of April, I still see the Orders created on the 1st of February and the 1st of March (January 1 and April 1 were both holidays).

Now the only part missing is the ">="

Can somebody lend me a hand there, please? I will have a look myself in the meantime, too.

Thanks a lot!

Best regards,

DataNibbler

Re: Two different date_fields

datanibbler — Fri, 04 May 2018 08:49:05 GMT

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 ...

Re: Two different date_fields

datanibbler — Fri, 04 May 2018 11:45:22 GMT

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

Re: Two different date_fields

datanibbler — Sun, 06 May 2018 15:30:23 GMT

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 ...

Re: Two different date_fields

jonathandienst — Mon, 07 May 2018 04:54:36 GMT

>>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!

Re: Two different date_fields

datanibbler — Mon, 07 May 2018 06:19:34 GMT

Thanks Jonathan!

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