topic SET ANALYSIS in QlikView

SET ANALYSIS

Anonymous — Fri, 08 Aug 2014 11:02:47 GMT

Hello Experts,

I am stuck with a problem and I need your help to resolve it

SUM( {$< DWH_IM_MOVEMENT_TYPE={'2'}, DWH_IM_DATE={$"GI_LAST_DATE"} >} GI_IM_QTY )

What I want to achieve is to return the GI_IM_QTY where DWH_IM_MOVEMENT_TYPE=2 and DWH_IM_DATE=GI_LAST_DATE and it calculates it for every item

in other words if we can write the set analysis of

SUM(IF(

DWH_IM_MOVEMENT_TYPE=2 AND

DWH_IM_DATE=GI_LAST_DATE,

GI_IM_QTY))

without any selection it will might solve the problem.

I will really appreciate is someone can help on this.

Re: SET ANALYSIS

Fri, 08 Aug 2014 11:30:44 GMT

=if(DWH_IM_MOVEMENT_TYPE='2',

SUM({$<DWH_IM_DATE={$"GI_LAST_DATE"} >} GI_IM_QTY ))

this will resolve your problem...

it will return the GI_IM_QTY where DWH_IM_MOVEMENT_TYPE=2 and DWH_IM_DATE=GI_LAST_DATE and it calculates it for every item

Re: SET ANALYSIS

jonathandienst — Fri, 08 Aug 2014 11:53:41 GMT

If GI_LAST_DATE is another field and you want to do row-by-row comparisons with DWH_IM_DATE, then set analysis will not work for you. Set analysis is applied to the entire model, much like selections made by the user, not on a row by row basis.

You have two options (1) continue using a sum(if()) expression or (2) modify your load script to add a flag field holding the result of the comparison, and then use the flag field in the set expression.

(1) works well if your data set is small and not too complex

(2) is better for large data sets where the performance of the sum(if()) expression gets poor

HTH

Jonathan

Re: SET ANALYSIS

PrashantSangle — Fri, 08 Aug 2014 12:09:40 GMT

Hi,

AS Jonathan said, It is correct that Set Analysis works at chart level not at dimension level.

So if you are comparing two date at each row then set analysis fails if they have diffferent value

Like if GI_LAST_DATE contain diffenrent value for each row then it your set analysis fails

so you can try with combination of IF an set anlysis would be better for you.

Try something like this

IF(DWH_IM_DATE=GI_LAST_DATE,sum({<DWH_IM_MOVEMENT_TYPE={'2'}>} GI_IM_QTY))

Or try 2nd solution provided by Jonathan.

Regards,

Re: SET ANALYSIS

Anonymous — Fri, 08 Aug 2014 12:24:46 GMT

Thanks Jonathan,

do you think something like this will work

if(DWH_IM_DATE='GI_LAST_DATE',if(DWH_IM_MOVEMENT_TYPE='2',sum(GI_IM_QTY))

I tried that but it shows -

Re: SET ANALYSIS

jonathandienst — Fri, 08 Aug 2014 12:28:23 GMT

I think you need:

Sum({$<DWH_IM_MOVEMENT_TYPE={'2'}>} If(DWH_I_DATE=GI_LAST_DATE, GI_IM_QTY))

You can still use the set expression for the movement type, but need a sum(if()) for the dates.

Re: SET ANALYSIS

Anonymous — Fri, 08 Aug 2014 14:29:42 GMT

Thanks Jonathan,

IF(DWH_IM_DATE=GI_LAST_DATE,sum({<DWH_IM_MOVEMENT_TYPE={'2'}>} GI_IM_QTY))

works fine but it takes the full sum sum of GI_IM_QTY i want just the last date to be selected.

what i want to achieve is regardless of selection it should take last_gi_date and what ever is the date should

give the result of this date like below.

Thanks

Re: SET ANALYSIS

Anonymous — Fri, 08 Aug 2014 14:37:38 GMT

Hi,

You might consider to create an additional flag in the data model:

IF(DWH_IM_DATE=GI_LAST_DATE,1) as FLAG_LAST_DATE

Then your expression will look like this:

sum({< DWH_IM_MOVEMENT_TYPE={'2'}, FLAG_LAST_DATE = {1} >} GI_IM_QTY)

Much simpler and efficient.

Regards,

Janusz

Re: SET ANALYSIS

Anonymous — Fri, 08 Aug 2014 16:54:31 GMT

The problem I am facing now is

DWH_IM_DATE is in a different tab and GI_LAST_DATE is in different tab of edit script.

GI_DATE_FLAG:

LOAD

DWH_SKU_REF_NO,

IF(DWH_IM_DATE=GI_LAST_DATE,1) as GI_DATE_FLAG

FROM $(PathData)z1_GI_LAST_DATE.QVD (qvd)

WHERE GI_LAST_DATE >= $(StartDate_Futura) AND GI_LAST_BRANCH=1000;

it gives an error

Re: SET ANALYSIS

Anonymous — Fri, 08 Aug 2014 16:57:52 GMT

Hi Jonathan,

how can i get the field from two different tab

GI_DATE_FLAG:

LOAD

DWH_SKU_REF_NO,

IF(DWH_IM_DATE=GI_LAST_DATE,1) as GI_DATE_FLAG

FROM $(PathData)z1_GI_LAST_DATE.QVD (qvd)

WHERE GI_LAST_DATE >= $(StartDate_Futura) AND GI_LAST_BRANCH=1000;

Re: SET ANALYSIS

aveeeeeee7en — Fri, 08 Aug 2014 17:05:53 GMT

Try this:

SUM({$<DWH_IM_MOVEMENT_TYPE={'2'},DWH_IM_DATE={'$(=Max(GI_LAST_DATE))'}>}GI_IM_QTY)

Regards

Aviral Nag

Re: SET ANALYSIS

Anonymous — Fri, 08 Aug 2014 17:24:43 GMT

Hi Aviral,

it looks like we are getting closer the problem is now it only shows you the GI_IM_QTY of the last day which is usually yesterday date anything before that it doesn't show you any stock.

I will be very thankful if you can help in this please