First 4 Weeks Sold Qty?

[devarasu07]

Hi All,

Trying to get first 4 Weeks and 8 week sold quantity using set analysis. kindly advise.

=sum({<Date={">=$(=Date(Min(Date)))<=$(=Date(Min(Date)+28))"}>} SoldQty)

[devarasu07]

Hi Giri,

Thanks for your solution, now it's excluding the week selection and gives expected output. but need to exclude year filter also, i've tried to exclude and it's working as expected.

=sum({<Year=,Week={">=$(=aggr(min({<Year=,Week=>}Week),ArticleNo)) <$(=aggr(min({<Year=,Week=>}Week),ArticleNo)+4)"}>} SoldQty)

also tried

=sum({<Year={'*'},Week={">=$(=aggr(min({<Week={'*'}>}Week),ArticleNo)) <$(=aggr(min({<Week={'*'}>}Week),ArticleNo)+4)"}>} SoldQty)

Note: once Article got launched in market then article 1st 4 week sold date always same and it's should not change. (we should exclude user week, year filter selection ).

Issue, if user select any other year then it's not giving correct result. need to exclude year filter selection as well. i've used below expression and its not correct output. do you have any idea. kindly advise. tks

!

[devarasu07]

Hi Manish,

Report has year and week filter, when ever they select any year and week then it should show respective output.

please refer to below screen image (expect o/p window). thanks

[sunny_talwar]

Issue, if user select any other year then it's not giving correct result. need to exclude year filter selection as well. i've used below expression and its not correct output. do you have any idea. kindly advise. tks

What is the issue still?

[devarasu07]

Hi Sunny,

Thanks for your prompt reply, want exclude year filter selection from below set analysis expression (other wise my expected output goes wrong) so i have tried to exclude year filter and below expression not considering the year filter exclusion. kindly advise. thanks

=sum({<Year={'*'},Week={">=$(=aggr(min({<Week={'*'}>}Week),ArticleNo)) <$(=aggr(min({<Week={'*'}>}Week),ArticleNo)+4)"}>} SoldQty)

Best Regard, Deva

[sunny_talwar]

May be you have to ignore it with the week modifier

=Sum({<Year={'*'},Week={">=$(=Only({<Year={'*'}>} aggr(min({<Week={'*'}, Year={'*'}>}Week),ArticleNo))) <$(=Only({<Year={'*'}>} aggr(min({<Week={'*'}, Year={'*'}>}Week),ArticleNo))+4)"}>} SoldQty)

[girirajsinh]

Hi Deva,

Following should give you what you are asking for,

Expression for First 4 Week sold quantity

=sum({<Year=,Week={">=$(=aggr(min({<Week=,Year=>}Week),ArticleNo)) <$(=aggr(min({<Week=,Year=>}Week),ArticleNo)+4)"}>} SoldQty)

BUT, I don't following that why do you need such result. You need to change your data model. Include a Year-Week Field. That gives the logically right first 4 weeks .

For now above expression should give you the result as you asked.

[devarasu07]

Hi Giri,

Tried suggested expression and still not working, please refer to attached document.

FYI, below screen user selected reporting year: 2017, week : 2 and article is: CK1-60360931 then need to calculate that article first 4 week sold qty,

able to find the 1st sold date for that article using below expression,

1st Week Launch Date(sold date) --> 5/10/2016

=min({<Year=,Week=>}Date)

now need to sum of sold qty up to 4 weeks  --> 1/11/2016

=(min({<Year=,Week=>}Date))+27

now i just want to sum of sold qty where Date between (5/10/2016 and 1/11/2016). and we should able to get below expected output.

,

[sunny_talwar]

Did you get a chance to try this?

=Sum({<Year={'*'},Week={">=$(=Only({<Year={'*'}>} aggr(min({<Week={'*'}, Year={'*'}>}Week),ArticleNo))) <$(=Only({<Year={'*'}>} aggr(min({<Week={'*'}, Year={'*'}>}Week),ArticleNo))+4)"}>} SoldQty)

or

=Sum({<Year,Week={">=$(=Only({<Year>} aggr(min({<Week, Year>}Week),ArticleNo))) <$(=Only({<Year>} aggr(min({<Week, Year>}Week),ArticleNo))+4)"}>} SoldQty)

[devarasu07]

Hi Sunny,

Tried and still not working. year filter exclusion not considering in our set analysis expression. do we have any other method (master calendar, variable etc) method to resolve? kindly advise. Thanks

Regards,Deva

[sunny_talwar]

I don't see any change in selection of Year in the two expressions. They stay 438 regardless of 2016 or 2017 selected. Is this not what you want?