Error..

[nikhilgarg]

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

[MK_QSL]

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

[nikhilgarg]

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

[teiswamsler]

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

[maxgro]

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

Use Aggregation Functions!

[nikhilgarg]

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.

[maxgro]

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

[nikhilgarg]

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.

[maxgro]

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)

[nikhilgarg]

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