topic Re: 1st 4 Week and 8 Week Sold Qty using Set Analysis in QlikView

First 4 Weeks Sold Qty?

devarasu07 — Wed, 25 Nov 2020 16:16:04 GMT

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)

Re: 1st 4 Week and 8 Week Sold Qty using Set Analysis

Anonymous — Mon, 16 Jan 2017 06:55:17 GMT

Since you are making selections, and qlikview's AQL system applies the selection to all the fields. So you get those values same as the value you get for Current week sales.

try this,

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

apply the same for the week sales also.

I hope this works.

Re: 1st 4 Week and 8 Week Sold Qty using Set Analysis

devarasu07 — Mon, 16 Jan 2017 07:11:37 GMT

Hi Anirban,

It's not working, week filter exclusion not working in below my expression.

Issue:

if We do week selection then 4 week sold quantity gives wrong result.

(i.e: below expression working if we don't select any weeks from week filter, but my requirement want to have dynamic week selection filter)

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

Thanks, Dev

Re: 1st 4 Week and 8 Week Sold Qty using Set Analysis

MK_QSL — Mon, 16 Jan 2017 07:17:22 GMT

Use these expressions

4 Weeks

=SUM({<Date = {">=$(=Date(WeekStart(Min(Date))))<=$(=Date(WeekStart(Min(Date))+27))"}>}SoldQty)

8 Weeks

=SUM({<Date = {">=$(=Date(WeekStart(Min(Date))))<=$(=Date(WeekStart(Min(Date))+55))"}>}SoldQty)

Re: 1st 4 Week and 8 Week Sold Qty using Set Analysis

girirajsinh — Mon, 16 Jan 2017 07:37:12 GMT

Please find attached qvw. It has the result you are looking for

Re: 1st 4 Week and 8 Week Sold Qty using Set Analysis

devarasu07 — Mon, 16 Jan 2017 07:39:07 GMT

Hi Manish,

Thanks for your kind reply, I've tried the same and it's working fine if we don't select any week number from week filter. but my requirement need to have week filter selection

below screen shot, left side image has correct result (without week selection), but if i do week selection then it's goes wrong.(refer right image). i already excluded week filter in set analysis but it's not considering. kindly adivse

4 Week ROS

=SUM({<Week=,Date = {">=$(=Date(WeekStart(Min(Date))))<=$(=Date(WeekStart(Min(Date))+27))"}>}SoldQty)

Re: 1st 4 Week and 8 Week Sold Qty using Set Analysis

devarasu07 — Mon, 16 Jan 2017 07:42:46 GMT

Hi Giri,

Same issue, when we do week filter selection then it's not working. any other suggestion. thanks

Thanks,Deva

Re: 1st 4 Week and 8 Week Sold Qty using Set Analysis

girirajsinh — Mon, 16 Jan 2017 08:21:19 GMT

hi Deva

Please find attached one. That should disregards the week selection

Re: 1st 4 Week and 8 Week Sold Qty using Set Analysis

MK_QSL — Mon, 16 Jan 2017 08:47:16 GMT

Let me have clarity on this?

Do you want to consider week selection?

What is the result you are looking for if you select Year 2016 and Week 53 with ArticleNo CK1-60360931?

Re: 1st 4 Week and 8 Week Sold Qty using Set Analysis

Anonymous — Mon, 16 Jan 2017 09:16:08 GMT

yeah got it. u don't have month filter, so exclude it. add week filter. and yes please make it 27 instead of 28 as u need 4 weeks including the starting date.

Re: 1st 4 Week and 8 Week Sold Qty using Set Analysis

devarasu07 — Tue, 17 Jan 2017 19:15:51 GMT

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

Re: 1st 4 Week and 8 Week Sold Qty using Set Analysis

devarasu07 — Tue, 17 Jan 2017 19:25:54 GMT

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

Re: 1st 4 Week and 8 Week Sold Qty using Set Analysis

sunny_talwar — Tue, 17 Jan 2017 19:46:30 GMT

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?

Re: 1st 4 Week and 8 Week Sold Qty using Set Analysis

devarasu07 — Tue, 17 Jan 2017 20:02:42 GMT

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

Re: 1st 4 Week and 8 Week Sold Qty using Set Analysis

sunny_talwar — Tue, 17 Jan 2017 20:05:47 GMT

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)

Re: 1st 4 Week and 8 Week Sold Qty using Set Analysis

girirajsinh — Tue, 17 Jan 2017 20:21:19 GMT

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.

Re: 1st 4 Week and 8 Week Sold Qty using Set Analysis

devarasu07 — Tue, 17 Jan 2017 20:47:03 GMT

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.

Re: 1st 4 Week and 8 Week Sold Qty using Set Analysis

sunny_talwar — Tue, 17 Jan 2017 20:51:05 GMT

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)

Re: First 4 Weeks Sold Qty?

devarasu07 — Tue, 17 Jan 2017 21:03:22 GMT

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

Re: First 4 Weeks Sold Qty?

sunny_talwar — Tue, 17 Jan 2017 21:04:38 GMT

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?