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: 
jag7777777
Contributor III
Contributor III

Set analysis question

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

13 Replies
jag7777777
Contributor III
Contributor III
Author

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?

alexandros17
Partner - Champion III
Partner - Champion III

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.

sunilkumarqv
Specialist II
Specialist II

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

jag7777777
Contributor III
Contributor III
Author

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