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 Sunny,

Please refer to updated attachment, in this attachment year filter exclusion from qlikview is not working. Thanks

if user select any week or year then this 4 Wks ROS value should not change.

attached article, just wanted to sum of Sold Qty from 1st sold qty to till 4th Sold Qty.

When we select Year: 2016 and below selection then output like below (sum of 5-Oct

[sunny_talwar]

I think it's a bad idea to use Aggr() in set analysis because if your going to look at this with more then one ArticleNo at a time, your set analysis expression isn't going to work.

Try this:

=Sum({<Year, Week>}Aggr(If(Only({<Year, Week>}Date) >= Min(TOTAL <ArticleNo> {<Week, Year>}Date) and Only({<Year, Week>}Date) < (Min(TOTAL <ArticleNo> {<Week, Year>}Date)+28), Sum({<Year, Week>}SoldQty)), ArticleNo, Date))

Now the First 4 weeks number isn't matching up, but selections on't impact the result. I see that the MinDate is 5/10/2016. Do you many this till 2/11/2016?

[devarasu07]

Hi Sunny,

Thanks, your latest expression is working find with single article (mock report ok) , but in my actual report has more than one article, do we have any other method solve this issue? . Thanks

Regards, Deva

[Colin-Albert]

Have a look at this blog on Relative Dates.

Relative Calendar Fields

Adding a DaysAgo or WeeksAgo field to your model would simplify the set expressions as the date calculation would be in the script not the expression.

[sunny_talwar]

The above expression isn't working for more than one ArticleNo? Really? that was the whole point of me offering you to use Aggr() instead of Set Analysis.

[devarasu07]

Hi stalwar1‌

I've tried your suggested expression and also like below but still that 4 & 8 Wks Sold Qty shows 0 and irrespective of user selection. but the result of 4 weeks ,8 weeks should based on user selected filter value. any suggestion on this expression? or can we do any other  simple way.? Thanks

4 Weeks Sold:

aggr(SUM({<Year={'*'},Month={'*'},Date ={"$(= '>=' & aggr(min(Date),Region,SubGroup,Countries,MaterialGrpDesc,ItemSubClass) & '<=' & aggr(Date((Min(Date))+28,'YYYY-MM-DD'),Region,SubGroup,Countries,MaterialGrpDesc,ItemSubClass) )"}>}SoldQty),Region,SubGroup,Countries,MaterialGrpDesc,ItemSubClass)

similarly for 8, 13, 20 Weeks Sold Qty.

Thanks,

Deva