topic Re: Top 50 Client Volume,Spend in QlikView

Top 50 Client Volume,Spend

Anonymous — Tue, 17 Nov 2015 20:50:00 GMT

Top 50 Client (volume) - categorization of the top 50 Client based on volume of transactions executed in the previous 12 months

Top 50 Client (spend) – categorization of the top 50 Client based on spend for the previous 12 months

I need to make these two dimensions.

I got following fields.

Client Name
Amount
Transaction ID
Transaction Type

Re: Top 50 Client Volume,Spend

robert_mika — Tue, 17 Nov 2015 22:35:40 GMT

Could you post your file?

Re: Top 50 Client Volume,Spend

Anonymous — Wed, 18 Nov 2015 06:25:01 GMT

The Data in the Tables are confidential information,that is the reason i cannot share it in here.

I appreciate if i get help without the file. Thanks

Re: Top 50 Client Volume,Spend

nagarjuna_kotha — Wed, 18 Nov 2015 06:30:35 GMT

Can u share sample data for this?

Regards,

Nagarjuna

Re: Top 50 Client Volume,Spend

Anonymous — Wed, 18 Nov 2015 06:43:52 GMT

How you are going to identify Volumn and Spends?

Any flag into Transaction type? if Yes, do the following:

Take Client Name as a Dimension in Bar chart(USed for Top Analysis) & set the Dimension Limit upto 50 into Dimension Limit tab::

and

Expression will give last 12 month Volumn:

Volumn =Sum({<Flag={'Volumn'} ,Date={">=$(=(MonthStart(Today(),-12)))<=$(=(Today()))"}>}Amount)

Similarly repeat for Top 50 Spend with following expression:

Spend: Sum({<Flag={'Spend'} ,Date={">=$(=(MonthStart(Today(),-12)))<=$(=(Today()))"}>}Amount)

Hope this will help!!

Re: Top 50 Client Volume,Spend

Wed, 18 Nov 2015 07:14:14 GMT

Hi ,

Do you need something like this for 1st requirement :

Top 50 Client (volume) - categorization of the top 50 Client based on volume of transactions executed in the previous 12 months

Dimension:Client

expression :count({<Date={">=$(=(MonthStart(Today(),-12)))<=$(=(Today()))"}>}Transaction ID)

Thanks

Khushboo

Re: Top 50 Client Volume,Spend

Anonymous — Wed, 18 Nov 2015 07:26:59 GMT

Hi Khushbo Do i need to put 'Date' function or Date_KEY Field in set Analysis ?

Re: Top 50 Client Volume,Spend

Anonymous — Wed, 18 Nov 2015 07:31:51 GMT

No there is no flag for Volume nor for Spend.

Re: Top 50 Client Volume,Spend

Anonymous — Wed, 18 Nov 2015 08:18:08 GMT

But There is a field

Top 100 ----Contents are ( Top 100,Top 300,Top 400,Top 500,Others)
Top 100-500 ----Contents are (Others, Top 100,Top 500)

Re: Top 50 Client Volume,Spend

Anonymous — Wed, 18 Nov 2015 08:34:06 GMT

How do you calculate your Volumn and Spend?

How you will decide, this trxn Amount is your Volumn and this is spend??

Re: Top 50 Client Volume,Spend

Anonymous — Wed, 18 Nov 2015 08:44:55 GMT

yes,

like: count({<YourDateField={">=$(=(MonthStart(Today(),-12)))<=$(=(Today()))"}>}Transaction ID)

also make sure format of YourDateField should be same as Today() Format

Re: Top 50 Client Volume,Spend

anlonghi2 — Wed, 18 Nov 2015 08:58:48 GMT

Hi,

please look at attached qvw.

I used sample data from the attached excel file and the pivot table show first 4 clients instead 50 to avoid to create a lot of sample data 🙂

Regards

Andrea

Re: Top 50 Client Volume,Spend

Wed, 18 Nov 2015 10:23:29 GMT

Hi Osama,

Please find the attached solution. Let me know if it helps.

Regards,

Vasim

Re: Top 50 Client Volume,Spend

Anonymous — Wed, 18 Nov 2015 14:06:58 GMT

Thanks alot Vasim.

Re: Top 50 Client Volume,Spend

Anonymous — Wed, 18 Nov 2015 14:10:04 GMT

Thanks alot.

Re: Top 50 Client Volume,Spend

Anonymous — Wed, 18 Nov 2015 14:17:10 GMT

I use the following set analysis:

For Top 50 Client (Spend)

Sum({<[Top 50]={'Top 50'},CalendarYear={'$(vPreviousYear)'}>}[Amount])

For Top 50 Client( Volume)

Count({<[Top 50]={'Top 50'},CalendarYear={'$(vPreviousYear)'}>}[Amount])