Skip to main content
Woohoo! Qlik Community has won “Best in Class Community” in the 2024 Khoros Kudos awards!
Announcements
Nov. 20th, Qlik Insider - Lakehouses: Driving the Future of Data & AI - PICK A SESSION
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Article wise store contribution

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

8 Replies
MK_QSL
MVP
MVP

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

Not applicable
Author

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

MK_QSL
MVP
MVP

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

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

Not applicable
Author

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

MK_QSL
MVP
MVP

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.

Not applicable
Author

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

Not applicable
Author

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

Not applicable
Author

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