Convert this into (working) set analysis please (I R FAIL)

[felix18807]

Calculate the sum of MyValue where the following conditions are met:

1. DatefieldA is greater than or equal to ADDMONTHS(FixedDateField,-12)
2. DatefieldA is less than FixedDateField
3. Min(DateFieldB) is less than FixedDateField

I can't seem to get the expression correct I'm afraid

[salto]

Hi,

sum ({<[DateFieldA]= {"<=$(=date(FixedDate))>=$(=date(ADDMONTHS(FixedDateField,-12)))"},

     Min(DateFieldB)= {"<=$(=date(FixedDate)) " } >} MyValue)

Hope this helps.

[#3]

Hi Rechard,

Check this out bro..

sum( {$<DatefieldA = {" >=$(=addmonths(FixedDateField,-12)) <$(=FixedDateField)"} ,FixedDateField = {"<$(=min(DatefieldA))"} >} Myvalue )

other wise If u want small expression then

take variables

v1 =addmonths(FixedDateField,-12)

v2 =FixedDateField

v3 =min(DatefieldA)

represent same expression like this

sum( {$<DatefieldA = {" >=$(v1)<$(v2)"} ,FixedDateField = {"<$(v3)"} >} Myvalue )

Regards,

koti

[felix18807]

Hi Salto,

That doesn't seem to work in situ (probably due to me not applying the code correctly).

I am trying to measure the performance of a catalogue of items against a basket of 3 months of sales. I am failing to generate the "MatchedValue" correct on the attached app.

I will continue to investigate but I always struggle with set analysis that is this complicated.

Many Thanks

Richard

[felix18807]

Thanks for that Koti I can certainly see how/why that should work.

I think it isn't working in the table because there are more factors involved than I have explained - completely my bad... I'm going to keep trying things.

Many Thanks

Rich

[salto]

Hi,

The error comes in the Min(DateFieldB) - which is Min(AddedToCart).

It seems not to be possible to use it in Set Analysis. So we need to set a Min(AddedToCat) in every ItemId.

Removing the min, it works:

sum ({<[Period]= {"<=$(=date(DateIsland))>=$(=date(ADDMONTHS(DateIsland,-3)))"}, AddedToCat= {"<=$(=date(DateIsland)) " } >} SalesVol)

But you need a date selected in date island field, otherwise it will always return 0.

Hth.

[felix18807]

It does help thanks Salto.

The issue is that on the live app there will be multiple AddToCat dates for different qualities of item and the end user needs to be able to make selections in Quality. So I need to be able to show the lowest AddedToCat for the selection across multiple qualities.

I'm thinking that it's probably something I'll have to try to resolve in the script.