Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Calculate the sum of MyValue where the following conditions are met:
I can't seem to get the expression correct I'm afraid
Hi,
sum ({<[DateFieldA]= {"<=$(=date(FixedDate))>=$(=date(ADDMONTHS(FixedDateField,-12)))"},
Min(DateFieldB)= {"<=$(=date(FixedDate)) " } >} MyValue)
Hope this helps.
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
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
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
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.
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.