topic Re: Error.. in QlikView

Error..

nikhilgarg — Fri, 11 Sep 2015 12:02:12 GMT

Hey,

I ahve a doubt that what is the difference between the following 2 expressions?

1: if(Date >= vRefernceDate, sum(Sales1))

2: Sum(If(Date >= vReferenceDate, Sales1))

In both above cases, for Date() i have by default Only(). Now, in my attached application, for ID 6, i have 2 different dates, then

why i am getting sumofsales2 value in last column ?

Thanks

Re: Error..

MK_QSL — Fri, 11 Sep 2015 12:05:25 GMT

2nd is right.. 1st one is wrong...

add one more line for

5, e, prod5, 701, 2015-04-24

Now you will realize that 701 is not coming up.

you need to use now

SUM(if(Date >= vRefernceDate, Sales1))

Re: Error..

nikhilgarg — Fri, 11 Sep 2015 12:11:36 GMT

Hey,

Plz find updated app. I was using wrong var in 2nd and 3rd exp. But what i want to know is the difference between:

1st expression and 2nd expression.

Thanks

Re: Error..

teiswamsler — Fri, 11 Sep 2015 12:18:25 GMT

hi Nikhil

If you have alot lines of data, concider using set analysis to optimise the reaction time in your front end charts

where i am from we have a rule never to use if statement in a chart expression

ex. Sum( {< Date = {">=$(vReferenceDate)"}>} Sales1)

/Teis

Re: Error..

maxgro — Fri, 11 Sep 2015 12:22:27 GMT

here you can find the difference between if(...) and sum(if....)

Use Aggregation Functions!

Re: Error..

nikhilgarg — Fri, 11 Sep 2015 12:27:50 GMT

Hey,

I ahve reffered same only but here it is written that:

1st expr: If will calculate per dimension and

2nd Expr: If will calculate per row.

But in my data if i put:

LOAD * INLINE [

Id, Name, Product, Sales1, Date

1, a, prod1, 101, 2015-01-01

2, b, prod2, 201, 2015-02-10

3, b, prod3, 501, 2015-02-21

3, b, prod4, 601, 2015-03-12

5, e, prod5, 701, 2015-04-14

5, e, prod5, 701, 2015-04-24

6, e, prod6, 202, 2015-04-28

6, e, prod6, 202, 2015-05-01

8, h, prod8, 801, 2015-05-12

9, h, prod9, 1101, 2015-05-21

10, g, prod10, 1001, 2015-06-10

];

Then for Id 6, i have same row data except Date. Then Only(Date) should not show value of sum(sales) in last column.

Re: Error..

maxgro — Fri, 11 Sep 2015 12:40:44 GMT

is last column

Sum( If(Date >= vRefernceDate, Sales1) )

For Id 6

I think both dates satisfy the if condition

You'll get the sum(Sales1) --> 202+202

Re: Error..

nikhilgarg — Fri, 11 Sep 2015 12:42:59 GMT

Hey,

Ya i know but if we go your way then , for 1st exp:

if(Date >= vRefernceDate, sum(Sales1))

Here also Date for Id = 6 satisfies condn. Then it should showdata.

Re: Error..

maxgro — Fri, 11 Sep 2015 12:58:38 GMT

in sum(if....) you sum the rows that satisfies the condition, 2 rows

6, e, prod6, 202, 2015-04-28

6, e, prod6, 202, 2015-05-01

in if(date>....................) the date is outside the sum; when there is no aggr function Qlik use only, the only(date) for the dimension value (id=6); when you have more than 1 date for one id, the only(date) is null and the condition isn't satisfied; add only(date) or concat(date, ', ') and you see many dates and a null for only(date)

Re: Error..

nikhilgarg — Fri, 11 Sep 2015 13:52:59 GMT

Hey THanks,

But i cann't understand one point that:

For below both expressions:

1: if(Date >= vRefernceDate, sum(Sales1))

2: Sum(If(Date >= vReferenceDate, Sales1))

There is no aggr function on Date, so in both cases , Only(Date) will be used, am i right ?

Thanks

Re: Error..

maxgro — Fri, 11 Sep 2015 14:10:15 GMT

i think yes for 1, no for 2

for 2 there is no need of an aggr function because the if is at the row level

also Sum(If(only(Date) >= vReferenceDate, Sales1)) seems incorrect because of 2 aggr functions

maybe hic‌

can help us to better understand

Re: Error..

hic — Mon, 14 Sep 2015 09:51:42 GMT

If(Date >= vRefernceDate, sum(Sales1))

This will be evaluated once per value in the dimension. Since Date is a naked field reference, "Only(Date)" will be used instead of "Date"

Sum(If(Date >= vReferenceDate, Sales1))

In this expression the If() function will be evaluated once per record in the source data, so an aggregation function in it would make no sense and is not possible.

HIC

Re: Error..

nikhilgarg — Mon, 14 Sep 2015 10:03:36 GMT

Hey ,

Thanks HIC but it my doubt may sound stupid but i ma not able to understand why

If(Date >= vRefernceDate, sum(Sales1))

This will be evaluated once per value in the dimension ?

Sum(If(Date >= vReferenceDate, Sales1))

In this expression the If() function will be evaluated once per record in the source data ?

I mean how can we tell that any expression will run once per value in dimension or once per record ?

Thanks

Re: Error..

hic — Mon, 14 Sep 2015 10:31:06 GMT

This is defined by the aggregation function. An aggregation function is not like other functions. An aggregation function is like a For-Next loop - looping over all records in the raw data. So, whatever is outside the aggregation function will be evaluated once per dimensional value; and whatever is inside will (in addition) be evaluated on row-level. Think of it as two loops, one nested in the other.

Below I have marked the aggregation functions in blue, and the expression inside the aggregation function in light blue.

If(Only(Date) >= vRefernceDate, Sum(Sales1))

Sum(If(Date >= vReferenceDate, Sales1))

See Aggregations and Function Classes

HIC

Re: Error..

nikhilgarg — Mon, 14 Sep 2015 10:57:18 GMT

Hey,

Thanks for the explaination HIC.

But, If whatever we write inside aggregation function is to be evaluated at row level then,

For: If(Only(Date) >= vRefernceDate, Sum(Sales1))

Date should be evaluated at row level instead at dimensional level becoz it is under aggregate function Only().

Please correct me if i ma wrong.

Thanks

Re: Error..

hic — Mon, 14 Sep 2015 11:36:45 GMT

The Only(Date) function is like a For-Next loop: It loops over all the rows, and for each row it checks the value of Date. But an aggregation function returns only one value per dimensional value - also if there are many input rows. The Only(Date) function returns Date if all rows have the same value, and NULL otherwise.

HIC