topic Re: Article wise store contribution in QlikView

Article wise store contribution

Tue, 29 Nov 2016 08:19:50 GMT

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))

Re: Article wise store contribution

MK_QSL — Tue, 29 Nov 2016 08:45:43 GMT

=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))

Re: Article wise store contribution

Tue, 29 Nov 2016 09:48:56 GMT

Thanks so much Manish for your reply.But the store count is not matching.for every article some nominal mismatch.

Re: Article wise store contribution

MK_QSL — Tue, 29 Nov 2016 10:01:16 GMT

Where you want this output? In text box or chart or table?

What are the other dimensions you are using in Chart or Table?

Re: Article wise store contribution

Tue, 29 Nov 2016 10:06:35 GMT

No other dimension.The output should be in Straight table.

Re: Article wise store contribution

MK_QSL — Tue, 29 Nov 2016 10:09:30 GMT

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.

Re: Article wise store contribution

Tue, 29 Nov 2016 10:49:22 GMT

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

Re: Article wise store contribution

Wed, 30 Nov 2016 04:25:43 GMT

Thanks Manish for your valuable reply...but still some mismatch in store count occurs.

Re: Article wise store contribution

Wed, 30 Nov 2016 04:26:22 GMT

Thanks Sukamal for your valuable reply.But still some mismatch in store count occurs.