topic Re: Expression for new orders using set analysis in QlikView

Expression for new orders using set analysis

Anonymous — Wed, 23 Nov 2016 13:53:23 GMT

Hey!

I need help to create an expression.

I have three tables, which i select from the database - Orders, Order_type and Clients.

1. First you have to insert right dates (I have calendars/sliders named date_range_start and date_range_end)

2. Now i want to know how many new orders i have for my order types (Starts in the date range and this client doesn't have any orders for two months ago) If client had an order for example three/four months ago then it's new order.

Result would be:

And I need to use EXPRESSIONS

So i tried to use set analysis but i don't know how to put it together.

I tried to get orders which start in the range, then subtract these (clients) who had orders in past two months AND NOW I HAVE TO SUBTRACT SOMETHING MORE OR CHANGE THE WHOLE COUNT, I don't know anymore

=Count({<START={"$(='>='& date_range_start& '<=' & date_range_end)"}>- <END={"$(='>='& MAKEDATE(year(date_range_start),MONTH(date_range_start)-2,day(date_range_start)) & '<' & date_range_start)"}>} OrderID)

I don't even know if I'm close to my solution or not. I have tried this so long now and I still don't have a solution.

I'm happy if somebody could help or guide me.

Re: Expression for new orders using set analysis

rubenmarin — Wed, 23 Nov 2016 14:06:04 GMT

Hi Kelly, dates are tricky and maybe formats are giving unexpected results, try removing the caption of the table and you will see the expression QV is constructing after expanding the $().

Some tips to make it easier:

- You can use Addmonths(date_range_start, -2) to retrieve the same date two months ago.

- You can do a Sum(StartConditions)-Sum(EndConditions), I think it should give the same result and you can check separatedly the values returned.

- To avoid date formats try using them as number:

=Count({<START={"$(='>='& Num(date_range_start)& '<=' & Num(date_range_end))"}>- <END={"$(='>='& Num(MAKEDATE(year(date_range_start),MONTH(date_range_start)-2,day(date_range_start))) & '<' & Num(date_range_start))"}>} OrderID)

Re: Expression for new orders using set analysis

stevedark — Wed, 23 Nov 2016 14:11:30 GMT

Hi Kelly,

I think that you probably need to be looking at the P() and E() functions in Set Analysis. These allow you to use the results of one expression when looking at another.

So, you would end up with something like:

=Count({<START={"$(='>='& date_range_start& '<=' & date_range_end)"},ClientId-=P({1<END={"$(='<'& Date(AddMonths(date_range_start,-2), 'D.MM.YYYY'))}>}ClientId)>} OrderID)

So, that is where the START is in your date range, but the client is not one who has ever ordered anything (regardless of selection) before two months ago.

A couple of important tweaks from what you had, if you were in Jan or Feb using your code the MAKEDATE would fail, AddMonths is better. Also, set analysis is really picky about date formats, so the Date function is essential.

I would suggest breaking the code down into little bits to test (e.g. test what is inside the P() separately) and you should be able to get there.

Good luck!

Steve

Re: Expression for new orders using set analysis

marcus_sommer — Wed, 23 Nov 2016 14:14:14 GMT

Assuming that start/end ranges are variables and have the same format like your START field you could try something like this:

=if(Count({<START={">=$(=addmonths('$(date_range_start)', -2)<='$(date_range_start)'"}>} OrderID) = 0,

Count({<START={">='$(date_range_start)'<='$(date_range_end)'"}>} OrderID))

whereby the if-part simplified the excluding-logic from the past periods. To get the expressions right follow the suggestions from Ruben regarding the expression-caption and the formattings.

- Marcus

Re: Expression for new orders using set analysis

stevedark — Wed, 23 Nov 2016 14:21:22 GMT

Marcus, would this not only work if in some kind of AGGR statement, perhaps on OrderID?

Re: Expression for new orders using set analysis

marcus_sommer — Wed, 23 Nov 2016 14:36:19 GMT

It's not a nesting of aggregation else only two counts within different parts of an if-loop and therefore should work.

- Marcus

Re: Expression for new orders using set analysis

Anonymous — Thu, 24 Nov 2016 12:52:46 GMT

Oh.

Thank you all.

Definitely going to use all yours tips in future.

For this problem I found the P() function most useful. I hadn't heard about the p and e function before.

But it gives me wrong number of started orders. For example just to try and see what p() is doing i tried:

=count({1<ClientID= p({1<Start={"$(='>='& date_range_start& '<=' & date_range_end)"}>} ClientID) >} DISTINCT OrderID)

It should show 1 order, but it shows 3 (Not using the same date ranges which are shown up)

But it is so close to my solution and I belive I can figure it out now.

So thank you all.

Re: Expression for new orders using set analysis

rubenmarin — Thu, 24 Nov 2016 12:58:38 GMT

Hi Kelly, note that you are filtering ClientID, from that (or those) ClientID it counts all different OrderID (without checking dates).

You can also use Concat (instead of Count or in another expression for debug) to see the different OrderID counted:

=Concat({1<ClientID= p({1<Start={"$(='>='& date_range_start& '<=' & date_range_end)"}>} ClientID) >} DISTINCT OrderID, '_')

Re: Expression for new orders using set analysis

Anonymous — Thu, 24 Nov 2016 13:00:24 GMT

Oh, Thanks.

Going to try this.

Re: Expression for new orders using set analysis

rubenmarin — Thu, 24 Nov 2016 14:17:23 GMT

Ok, but what I said that only for debug, not the solution.. after a slow reading of your post I think you already know wich ones are the extra OrderIDs so my last post has no use.

What I tried to say is that you also need to set something that filters Orders (it can be done adding an Order field to set analysis like OrderID, START, END...)

Re: Expression for new orders using set analysis

Anonymous — Thu, 24 Nov 2016 14:40:21 GMT

Your last post was very helpful, now i can see what orders it counts.

And it doesn't make sense, the function counts orders even from last year, it doesn't depend on my date range.

Yes, it shows only those clients orders who has END in the range but counts orders wrongly.

Re: Expression for new orders using set analysis

Anonymous — Thu, 24 Nov 2016 14:42:08 GMT

I think I got something that works now. I tried to use somekind a intersection.

=count({1<ClientID= p({1<Start={"$(='>='& date_range_start& '<=' & date_range_end)"}>} ClientID),

Start={"$(='>='& date_range_start& '<=' & date_range_end)"}>} DISTINCT OrderID)

Thank you so much!