SET ANALYSIS

[Anonymous]

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.

[#2]

=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

[jonathandienst]

Hi

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

[PrashantSangle]

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,

[Anonymous]

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 -

[jonathandienst]

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.

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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;