topic Re: Set analysis question in QlikView

Set analysis question

jag7777777 — Fri, 20 Nov 2020 18:02:30 GMT

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

Re: Set analysis question

alexandros17 — Fri, 09 Jan 2015 08:54:49 GMT

I think that correct syntax should be

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

Let me know

Re: Set analysis question

Colin-Albert — Fri, 09 Jan 2015 08:58:34 GMT

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

Re: Set analysis question

jag7777777 — Fri, 09 Jan 2015 09:02:18 GMT

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

Re: Set analysis question

jag7777777 — Fri, 09 Jan 2015 09:04:25 GMT

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

Re: Set analysis question

alexandros17 — Fri, 09 Jan 2015 09:11:22 GMT

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

Re: Set analysis question

jag7777777 — Fri, 09 Jan 2015 09:19:28 GMT

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

Re: Set analysis question

alexandros17 — Fri, 09 Jan 2015 09:23:25 GMT

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

Re: Set analysis question

jag7777777 — Fri, 09 Jan 2015 09:36:25 GMT

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?

Re: Set analysis question

SunilChauhan — Fri, 09 Jan 2015 09:39:02 GMT

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

Re: Set analysis question

jag7777777 — Fri, 09 Jan 2015 09:41:44 GMT

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?

Re: Set analysis question

alexandros17 — Fri, 09 Jan 2015 09:43:13 GMT

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.

Re: Set analysis question

sunilkumarqv — Fri, 09 Jan 2015 10:22:58 GMT

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

Re: Set analysis question

jag7777777 — Tue, 13 Jan 2015 11:57:25 GMT

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