Can

can some one help me .

May be

may be The way I ask the question many of you don't understand . Let me make it simple. How to track customer start buying date and list the name by column by month and fill the name in te cell. Paul

Hi,

I’m not sure I fully understand the requirement but one way you might try is to create a Pivot Chart and add the following elements;

Dimensions:

First Dimension is ‘Date Start To Buy’

Second Dimension is a Calculated Dimension of same date ‘=’Date Start To Buy’’

Third Dimension is ‘Company Name’

Expressions:

=MaxString(CompanyName)

(Could also add a Sales total or some value)

Presentation Tab:

Un-tick ‘Suppress Zero-values’, ‘Suppress Missing’ and ‘Populate Missing Cells’ so it leaves the cells empty (from your example I’m assuming cells like C3 and C4 will always be empty).  Change the ‘Missing Symbol’ to blank.

Then click Apply/Ok and use the mouse to click/select the Second Dimension and drag it up to the top right corner of the chart to create a cross-tab type layout. 

You will only get results for the diagonal strip where the dates match – like your example.

Only problem left is that Third Dimension – ‘Company Name’ still shows down the left hand side – you may want to hide that somehow.  Maybe make that column narrow and change the background/font colour to hide the text.

Perhaps someone else has a more elegant solution.

I’d be interested to know why your users like that sort of layout, won’t most of the cells be empty ?,

H.

Hi Sir ,

thank you very much for your advise.

http://www.box.net/shared/px9a3s7v5u

I am almost getting there , i need your advise on 3 point :-

1. How to add one more col to display total number of number customer generated.

2. How to round up the month start buy variable to with out any decimal point.

3.some of the company fail in the same period it does not able to display , how to make one cell display more then 1 company ?

I am the developer , I need this report to let my sales staff know that generate new account is very impt in order to acheive their sales target.

Paul

Paul,

Some suggestions;

1. Try ticking 'Partial Sums' on the 1st Dimension and change the expression that does the MaxString() to have a condition around it
to use Dimensionality () to check what level it's at - for Dimensionality () = 0, the Grand Total level, do a Count(Distinct Company Name)
and for anything else do MaxString().

e.g. something like '=If (Dimensionality()=0,Count(Distinct Company_Name),MaxString(Company_Name))'

That would give you a count of companies per month/date at the foot of the column of names.

2. If you're using the number format setting of 'Money' (on the Number tab) remove the '.00' from
the Format Pattern and the Round function (in script or expression) will round values as specified. 

3. I don't know the answer here - I can't download files at the moment so can't see exactly what you've
built so far. From the sound of it though, maybe add another dimension which is a 'Fail'/'Sold' flag and add to Pivot within 1st Dimension.

Regards,

H. 

Hi Sir,

Thank you for give me the code that can able to perform 1 stone kill 2 bird, which able to give the total number of new customer , Now i manage to make the table more simplify as below for check new customer contribution by period , this is what i need :-

Now i have one problem , that is if i want to filter those new customer start buying in 2009 , how many ? i use filter by year for year = 2009, but this time table also display those customer start buy in 2006. it there a way for me only filter 2009 start buy customer only ?

Below is my QV doc. 

http://www.box.net/shared/1zv5d8zj13

Paul,

I saw a clever piece of code in a shared Qlikview on YTD analysis (by Jason Long) that may be useful.

http://community.qlikview.com/media/p/82595.aspx

On the tab 'Set Analysis - Multiple Dates' 2 different dates are used in Set analysis to do 2 versions of the sum(salesamount) based on the
same selected Year and Month.

Perhaps you could adapt your MaxString(Company Name) expression to use something similar, i.e. restrict the set on 'Pay Start Year' using the selected Year (which is a different date ?).

Regards,

H.

Hi sir , Thank you for your reply, and provide me the link , i have already try to play with the set analysis and i have use some of the code with the user here help. as Set analysis is a complicated and powerfuil .

In fact i have ask this question on below link ( which is also question on how to use set analysis to get the new and lost customer ) , but till now i still waiting and hope that some one help me on this.

http://community.qlikview.com/forums/t/31112.aspx

Last evening when i swimming , i found a solution which is create a variable that flag new acount by year , which it will work. but for 10 year , i need 10 variable . and this code also need a lot of maintenance.

  If(sum(if(Year_n=1,sales)) <> 0 AND NumSum((sum(if(Year_n=2,sales)) + (sum(if(Year_n=3,sales))))) = 0, 'Yes', 'No') as NA_00,

if Set can do it , it will be wonderful.

Paul