Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello friends,
I have a requirement.
I have a dimension-Article
I want to count of stores where article contribution storewise is <20%
i.e-article 1 has sells in store1,store2,store3 where the store wise contribution is respectively 30%,60%,10% total 100%
then Article 2 has sells in store2,store4,store5 where the store wise contribution is respectively 15%,70%,15% total 100%
I want to count those distinct stores where this contribution% is <20%,for article 1 -10% i.e-Store3
for article 2 -10% i.e-Store2,store5
I have written the expression for contribution% <20% is:
=if(sum({<@_Date={">=$(vFinStartDateCY)<=$(vMonthEndCY)"},@_Month=,@_FinQuarter=,@_FinFullYear=>}Sale)
/
sum(Total<Article>{<@_Date={">=$(vFinStartDateCY)<=$(vMonthEndCY)"},@_Month=,@_FinQuarter=,@_FinFullYear=>}Sale)<'20.00%',
sum({<@_Date={">=$(vFinStartDateCY)<=$(vMonthEndCY)"},@_Month=,@_FinQuarter=,@_FinFullYear=>}Sale)
/
sum(Total<Article>{<@_Date={">=$(vFinStartDateCY)<=$(vMonthEndCY)"},@_Month=,@_FinQuarter=,@_FinFullYear=>}Sale))
=SUM({<@_Date={">=$(vFinStartDateCY)<=$(vMonthEndCY)"},@_Month=,@_FinQuarter=,@_FinFullYear=>}Aggr(IF(SUM({<@_Date={">=$(vFinStartDateCY)<=$(vMonthEndCY)"},@_Month=,@_FinQuarter=,@_FinFullYear=>}Sale)
/
SUM({<@_Date={">=$(vFinStartDateCY)<=$(vMonthEndCY)"},@_Month=,@_FinQuarter=,@_FinFullYear=>}Total <Article> Sale)<0.2,1,0),Article,Store))
Thanks so much Manish for your reply.But the store count is not matching.for every article some nominal mismatch.
Where you want this output? In text box or chart or table?
What are the other dimensions you are using in Chart or Table?
No other dimension.The output should be in Straight table.
Create a Straight Table
Dimension
Store
Expression
=SUM(Aggr(IF(SUM(Sale)/SUM(Total <Article> Sale)<0.2,1,0),Article,Store))
Now it's upto you to add set analysis. I will not try for that. The above should work without set analysis.
Hi Debasmita,
Expression :
=Count( DISTINCT if(aggr(sum(#_NETAMOUNT)/sum(TOTAL<@_INV_ARTICLE> #_NETAMOUNT),%_SITE_DIM_ADMSITECODE)>0.1,@_INV_ARTICLE,%_SITE_DIM_ADMSITECODE))
Please add your set analysis in the expression.
Regards,
Sukamal
Thanks Manish for your valuable reply...but still some mismatch in store count occurs.
Thanks Sukamal for your valuable reply.But still some mismatch in store count occurs.