topic set analysis column calculation in QlikView

set analysis column calculation

Tue, 22 Oct 2013 12:40:37 GMT

Hi everyone!

I need some help on summarizing days which our delivery date differ close date.

Here is the task:

if wanted delivery date is '-' skip the record, then if wanted delivery date is greater than close date of shop order then the number of late days = 0, if wanted delivery date is less than close date of shop order then calculate the number of late days.

[WANTED_DELIVERY_DATE] and [CLOSE_DATE] are fields from separated tables. So that's why i trying to use Set Analysis, but i facing incorrect calculations when using expression:

=sum({$<[WANTED_DELIVERY_DATE]-={"$(=isnull(WANTED_DELIVERY_DATE))"},

[WANTED_DELIVERY_DATE]={">$(=date(DayStart(CLOSE_DATE)))"}>} $())

where

vDateDiff=Interval(date(DayStart(CLOSE_DATE))-date(WANTED_DELIVERY_DATE),'d')

But, when i'm typing [WANTED_DELIVERY_DATE]={">$(=date(DayStart(2013-09-04)))"}

everything goes perfect.

Could you tell me what is wrong?

Appreciate answers and assumes!

Re: set analysis column calculation

jonathandienst — Tue, 22 Oct 2013 12:54:11 GMT

I assume that you are trying to use this calculation in a table or chart, and that WANTED_DELIVERY_DATE and CLOSE_DATE are two fields that are associated with each other (a key between the tables):

Then you cannot do this using set expressions. The set expression is evaluated outside the context of the table (ie before the table is built) and has no knowledge of the dimensions. So simply referencing CLOSE_DATE will return null. When you enter a literal date, QV can evaluate this correctly as you have seen.

You can do this using sumif...

Sum(If([WANTED_DELIVERY_DATE]>CLOSE_DATE, $(vDateDiff)))

Regards

Jonathan

Re: set analysis column calculation

Tue, 22 Oct 2013 13:09:35 GMT

Dankie Jonathan, got the right results.

Well, i have changed [WANTED_DELIVERY_DATE]={">$(date(DayStart(CLOSE_DATE)))"}>} $(vDateDiff)

(removed "=" sign) and it also worked fine(haven't tested).

i have one more question to you-where can i read all the "Logic and architecture stuff" about QV, i mean something like as you said "The set expression is evaluated outside the context of the table".

Thanks!

Re: set analysis column calculation

jonathandienst — Tue, 22 Oct 2013 14:34:23 GMT

When QV calculates a table, it evaluates the table expressions for each combination of the dimensions of the table.

If you use an aggregation expression (eg sum(), min(), max(), etc), then QV can evaluate that for each row of a table, and also for the total row.

If you use just a field name in an expression without an aggregation, QV may be able to evaluate the expression if and only if there is only one possible value for that field in the context in which the expression runs. The context may be a row calculation in a table, a partial total, a total or outside of the table, in a text box for example). If there is more than one possible value in that context, then the expression will return null, as QV cannot determine which of the possible values to use.

Set expressions are evaluated outside the context of the table - or, if it is easier to understand, before the table dimensions are calculated. This similar to evaluating the expression in a text box. Therefore, if a set expression refers to a non-aggregated field on the RHS of the expression, it will only work if there is only one possible value for that field, for example, by selecting a value in a list box. If there is more than one possible value, the set expression will fail. The chart expression may calculate, but the result will be as if the set expression was not there at all.

I hope that clarifies it rather than confusing you further

Jonathan

Re: set analysis column calculation

Thu, 31 Oct 2013 15:48:28 GMT

Jon,

i actually had one value for each record, so that worked fine.

Thank you for your answer and i appreciate your explanation on how QV works.