Set Analysis in QlikView - value for previous year

[lukas_sokol]

Hi,

I have the following problem

I want to have sum of OrderValue only when orderqty 2013,2014 and orderValue 2013,2014 is <> 0

I try:

if(

    Sum({$< Year ={$(PrevYear)} >}OrderedQty) <> 0 and

    Sum({$< Year ={$(PrevYear)} >}OrderValue) <> 0 and

    sum( {$<Flag_OrderValue = {1}>} OrderedQty) <> 0 and

    Sum( {$<Flag_OrderValue = {1}>} OrderValueBase) <> 0,

     Sum(OrderValue)

  )

[Anonymous]

Hi,

can you post sample data?

Regards.

[lukas_sokol]

Sample data is presented in the picture above.

Unfortunately I can't add report file because it contains sensitive data

[lukas_sokol]

Only Total value is incorrect.

Should be 211+1605 = 1816.

In this table a have also 2 dimensions, Supplier and Item

[Anonymous]

Yes, I see, with a qvw file it is better. Now I try to see what is the problem.

[Anonymous]

Can you try with:

if(

    Sum({$< Year ={$(PrevYear)} >}OrderedQty) <> 0 and

    Sum({$< Year ={$(PrevYear)} >}OrderValue) <> 0 and

    sum( {$<Flag_OrderValue = {1}>} OrderedQty) <> 0 and

    Sum( {$<Flag_OrderValue = {1}>} OrderValueBase) <> 0,

     Sum(OrderValue) ,0

  )

[lukas_sokol]

yes, unfortunately I also tried in this way

[Anonymous]

Without sample data (I understand the data is sensitive but I mean a load inline with the table) is a guessing game.

Really you cant provvide a test?

[PrashantSangle · Accepted solution]

Hi,

try

Sum(if(

    Sum({$< Year ={$(PrevYear)} >}OrderedQty) <> 0 and

    Sum({$< Year ={$(PrevYear)} >}OrderValue) <> 0 and

    sum( {$<Flag_OrderValue = {1}>} OrderedQty) <> 0 and

  Sum( {$<Flag_OrderValue = {1}>} OrderValueBase) <> 0,

     OrderValue )

  )

or

use aggr()

like

sum(AGGR(YourExpression,Dimension1,Dim2,Dim3))

Regards

[jonathandienst]

If this is a straight table, change the Total to Sum of Rows on the Expression dialog.

If this is a pivot table, you need an expression like:

Sum(Aggr(If(

    Sum({$< Year ={$(PrevYear)} >}OrderedQty) <> 0 and

    Sum({$< Year ={$(PrevYear)} >}OrderValue) <> 0 and

    sum( {$<Flag_OrderValue = {1}>} OrderedQty) <> 0 and

    Sum( {$<Flag_OrderValue = {1}>} OrderValueBase) <> 0,

    Sum(OrderValue)

  ), <dim1>, <dim2>, ....))

Where <dim1>, <dim2>, .... are a comma separated list of the dimensions in your table. Eg

...

), ProductCode, Location))