topic Re: Convert this into (working) set analysis please (I R FAIL) in QlikView

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

felix18807 — Fri, 29 Nov 2013 12:30:16 GMT

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

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

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

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

salto — Fri, 29 Nov 2013 12:38:13 GMT

Hi,

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

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

Hope this helps.

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

Fri, 29 Nov 2013 12:51:56 GMT

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

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

felix18807 — Fri, 29 Nov 2013 13:00:25 GMT

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

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

felix18807 — Fri, 29 Nov 2013 13:15:39 GMT

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

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

salto — Fri, 29 Nov 2013 13:18:03 GMT

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.

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

felix18807 — Fri, 29 Nov 2013 14:09:14 GMT

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.