topic Re: Set Analysis in QlikView - value for previous year in QlikView

Set Analysis in QlikView - value for previous year

lukas_sokol — Tue, 30 Jun 2015 09:00:51 GMT

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)

)

Re: Set Analysis in QlikView - value for previous year

Anonymous — Tue, 30 Jun 2015 09:05:38 GMT

Hi,

can you post sample data?

Regards.

Re: Set Analysis in QlikView - value for previous year

lukas_sokol — Tue, 30 Jun 2015 09:09:46 GMT

Sample data is presented in the picture above.

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

Re: Set Analysis in QlikView - value for previous year

lukas_sokol — Tue, 30 Jun 2015 09:11:47 GMT

Only Total value is incorrect.

Should be 211+1605 = 1816.

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

Re: Set Analysis in QlikView - value for previous year

Anonymous — Tue, 30 Jun 2015 09:14:40 GMT

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

Re: Set Analysis in QlikView - value for previous year

Anonymous — Tue, 30 Jun 2015 09:19:30 GMT

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

)

Re: Set Analysis in QlikView - value for previous year

lukas_sokol — Tue, 30 Jun 2015 09:22:21 GMT

yes, unfortunately I also tried in this way

Re: Set Analysis in QlikView - value for previous year

Anonymous — Tue, 30 Jun 2015 09:25:08 GMT

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?

Re: Set Analysis in QlikView - value for previous year

PrashantSangle — Tue, 30 Jun 2015 09:27:24 GMT

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 )

)

use aggr()

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

Regards

Re: Set Analysis in QlikView - value for previous year

jonathandienst — Tue, 30 Jun 2015 09:27:43 GMT

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

Re: Set Analysis in QlikView - value for previous year

lukas_sokol — Tue, 30 Jun 2015 09:32:07 GMT

using

sum(AGGR(YourExpression,Dimension1,Dim2,Dim3)) give to correct value