Set analysis question

[jag7777777]

Hi all,

Hoping someone can help with the syntax on this.

I'd like to create some historic comparisons of old orders to compare against my new order book in graphs, charts etc..

As the user  is going to choose the day, month and year for the current order book (which is tomorrow's order) we have no history for this selection. Add to that the fact I want to render last week's, last month's and last year's data - I know I definitely want set analysis for my expressions.

I'm not getting the expected result from my expression when I start to add the date logic. I've proved it works when just using the transaction type modifier.

Things to note are the user doesn't actually select [order date]. Instead, they choose a day no, month and year from three list boxes (arranged as horizontal controls). Not sure if this is the issue - but I have tried using this approach in my set analysis too (i.e. using modifiers for all three variables).

Key fields are:

Transaction Type = tag for each type of data row. I only want history records

Order date = the order date of both sales history and order book records

My current syntax is as follows:

=sum({<
[Transaction type]={'Sales history'},
[Order Date] = {"=DATE([Order date]-7)"}

>}
[Tonnes])

When I run the =DATE([Order date]-7)  in a standard text box it works fine - so I'm guessing it's something to do with scope or evaluation order?

As said above, the order date isn't actually selected by the user - but there is a selection on three other fields

Order Day No

Order Year

Order Date Abbr Month

So, I've also tried setting these all to empty in my expression, but this has just summed the entire tonnage of my data set....?

=sum({<
[Transaction type]={'Sales history'},
[Order Date] = {"=DATE([Order date]-7)"},
[Order Day No]=,
[Order Date Abbr Month]=,
[Order Year]=

>}
[Tonnes])

Any help would be greatly appreciated.

Kind regards,

John

[alexandros17]

I think that correct syntax should be

=sum({<
[Transaction type]={'Sales history'}, [Order Date] = {'$(=DATE([Order date]-7))'} >} [Tonnes])

Let me know

[Colin-Albert]

you can use makedate() to create a date from year, month & day fields and include this in your set expression.

e.g. makedate(year, month,day)            substituting your actual fields for year, month & day

sum({<[Transaction type]={'Sales history'}, [Order Date] = {'$(=makedate(year, month, day)- 7) '} >} [Tonnes])

[jag7777777]

Thanks for the reply Alessandro. Unfortunately, this still returns a 0. I've tried adding to your suggestion clearing the three selected fields - but this then gives me the entire tonnage again....

[jag7777777]

Thanks for the reply Colin. I'm tempted to try this - but I also want to create expressions for last month and last year figures - and I already have my expressions on standby with AddMonths and AddYears.....?

Can I subtract months and years using make date do you know?

Kind regards,

John

[alexandros17]

Surely the problem comes from the date format and /or transformation, try your set analysis replacing

'$(=DATE([Order date]-7))' with a fixex date (like 2014/12/31 or 31/12/2014 ...)

let me know

[jag7777777]

Thanks Alessandro - I think we're getting closer.

When I use the below, I just get the sum of sales history tonnes - i.e. it seems to be ignoring the order date:

=sum({<
[Transaction type]={'Sales history'},
[Order Date] = {'$(=DATE(2014/01/08))'} >}
[Tonnes])

Any ideas...?

[alexandros17]

Try using only [order date] without using the function date ,...

[jag7777777]

IS my date format correct - sorry, but I'm used to TSQL and don't have any experience of explicitly entering dates...

=sum({<
[Transaction type]={'Sales history'},
[Order Date] = {'2015/01/01'} >}
[Tonnes])

I run the above next to another text box which just has =sum(Tonnes]) in it.

The difference between the two is just the transaction type - i.e. one is the sum of just sales history tonnes and the other is the sum of all tonnes. The date is being completely ignored?

[SunilChauhan]

check Orderdate format  and then put in bracket

if  its YYYY/DD/MM then

[sum({<
[Transaction type]={'Sales history'},
[Order Date] = {'2015/01/01'} >}
[Tonnes])

i will suggets you could use YYYY-MM-DD or DD-MM-YYYY format