Load clients with no turnover

[avantime]

Hi all,

Right now I am loading clients with turnovers from a qvd file like:

Load

[Client ID],

[Invoice date],

[Invoice number],

Turnover,

Year([Inovice date]) as Year,

Month([Invoice date]) as Month

From (qvd);

This works ok, but I would also like to see the clients that have no turnover.

For this I figured that I should concatenate the invoices database with the clients database, but I have not succeeded in having a good result. The rest of the clients should show up with 0 as turnover for each month in which they had no turnover.

Any ideas?

Thanks a ton!

[swuehl]

Not exactely sure what you want to achieve.

You can create a matrix of all customers, years and months by JOIN'ing all year month combinations (which you can load from your QVD or generate e.g. with a master calendar) to the complete list of customers loaded from your customer table:

TMP:

LOAD DISTINCT Year, Month

RESIDENT YourFactTable;

JOIN

LOAD ClientID

FROM Customer.qvd (qvd)

STORE TMP INTO YearMonthClient.qvd (qvd);

DROP TABLE TMP;

Now you can concatenate or join this table to your fact table to have a fact table with all combinations of Year, Month and Customer (if this is what you were asking for):

CONCATENATE (YourFactTable)

LOAD ClientID, Year, Month

FROM YearMonthClient.qvd (qvd);

[avantime]

Hi Stefan,

InvoicesDatabase.qvd contains only clients that purchased something at least one time during said period.

Now if I want to check which clients have a lower than X total revenue during said period, I will only see clients with a total revenue lower than X but not clients that did not purchase anything at all.

In my mind the solution is to cross check the clients in InvoicesDatabase.qvd with the complete clients database.

For example I have the following database of invoices for a defined period:

ClientID	Revenue
A	100
E	24
F	23
I	22
K	32

But I have clients A,B,C,D,E,F,G,H,I,J,K,L

So the table should look like:

ClientID	Revenue
A	100
B	0
C	0
D	0
E	24
F	23
G	0
H	0
I	22
J	0
K	32
L	0

Hope you got it

Thanks!

[swuehl]

I think that's basically what my last post should return, when using an expression like

=Sum(Revenue)

and dimensions like ClientID, Year and Month.

In your specific example with only client and Revenue, you also use something like

LOAD

     ClientID, Revenue

FROM YourFactTable.qvd (qvd);

CONCATENATE

LOAD ClientID, 0 as Revenue

FROM YourClientTable.qvd (qvd)

WHERE NOT EXISTS(ClientID);

[avantime]

I`ll try it tomorrow, let you know how it goes. Thank you!

[avantime]

I tried your solution Stefan, it works OK until I select a date, then clients with 0 turnover dissapear.

If no date is selected, then I can see clients with 0 turnover.

On any given date I select, I want to see a list complete with clients with turnover and clients with no turnover.

Right now I realize that maybe concatenation is not the way to go, given the fact that if I load the db with clients turnover in 2016 and concatenate it with the clients db it will add only clients with no turnover in 2016, not clients that had no turnover on a specific day, for example.

Maybe a formula?

[avantime]

Back to my problem.. It remains unresolved. Any fresh ideas?

[timsaddler]

You need to force a nominal zero 0.00001 for each timeframe (presume month?) to trigger the field in the report maybe

[avantime]

Hi, thanks for the answer!

Can you please direct me on how I can do that? Thank you!

[sunny_talwar]

How about this:

=Sum(Revenue) + Avg({1<ClientID = P(ClientID)>} 0)