Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi Alessandro,
This returns the right answer - but it's not great for when I want to get last year's, last months and last week's and we end up straddling months and years:
[Transaction type]={'Sales history'},
[Order Day No] = {1},
[Order Date Abbr Month] = {Jan},
[Order Year] = {2015}
>}
[Tonnes])
I guess it does prove that these three selected fields could be another approach though?
I guess I'd need to use the 'only' keyword somewhere to ensure only one month, day and year is ever selected?
Not sure if this helps?
I've seen again your expression:
[Order Date] = {"=DATE([Order date]-7)"} as you said (and I agree) the problem is here.
Set analysis symtax work in this way: Field = Value(s) but these values cannot be explicited by the same or another field that is you cannot write Field = Field+1, it doesn't work. Field = Max(field) for example works because on the right side you are expliciting only one value (a fixed value)
So the mistake is this, you should try to replace the right side with the getfieldselection([order date]) for example or you could compute a new field in the script and use it as a flag.
Hope it helps you, do not hesitate to ask more.
hence create DateID in your calender for best practice
In calender script
Autogeneate128(Monthend(OrderDate)) as DateID
later use DateID in Expression
=sum({<
[Transaction type]={'Sales history'},
DateID = {$(=Max(DateID)-7)},
[Order Day No]=,
[Order Date Abbr Month]=,
[Order Year]=
>}
[Tonnes])
Hi All,
Our QlikView partners, Ometis, pointed us in the right direction in the end. They suggested using the FLOOR function on our timestamp field to create a date field in our dataset.
Once I did this, I was able to use the following set analysis script:
=sum({<
[Transaction type]={'Sales history'},
[Order date] = {$(=$(vLastWeek))},
[Order Day No]=,
[Order Year] = ,
[Order Date Abbr Month]=
>}
[Tonnes])
Where vLastWeek can be interchanged with vLastYear, vLastMonth etc...
Variables are declared as:
vLastWeek -
(only([Order date])-7)
vLastMonth -
NUM(AddMonths(only([Order date]),-1,0))
vLastYear -
NUM(AddYears(only([Order date]),-1))
Thought I'd post the solution in case it helped anyone else.
Kind regards,
John