Expression for new orders using set analysis

[Anonymous]

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.

[rubenmarin]

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)

[stevedark · Accepted solution]

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

[marcus_sommer]

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

[stevedark]

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

[marcus_sommer]

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

- Marcus

[Anonymous]

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.

[rubenmarin]

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, '_')

[Anonymous]

Oh, Thanks.

Going to try this.

[rubenmarin]

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