Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
datanibbler
Champion
Champion

Two different date_fields

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

1 Solution

Accepted Solutions
jonathandienst
Partner - Champion III
Partner - Champion III

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

14 Replies
kamal_sanguri
Specialist
Specialist

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

datanibbler
Champion
Champion
Author

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

kamal_sanguri
Specialist
Specialist

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

Capture.PNG

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

datanibbler
Champion
Champion
Author

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

datanibbler
Champion
Champion
Author

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

datanibbler
Champion
Champion
Author

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.

datanibbler
Champion
Champion
Author

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

Worth a shot ...

jonathandienst
Partner - Champion III
Partner - Champion III

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
datanibbler
Champion
Champion
Author

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