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!

[johnw]

If I've understood (I admit to skimming), it seems to be working for me by having both an invoice and a client table, and in the chart dimension saying to show all values, suppress null, and then on presentation don't suppress zero-values. See attached.

[avantime]

Nope.. Not working, same result as before

[avantime]

Hi John,

This doesn`t work for me, as I also have Month and Year which I use in the pivot dimensions.

Invoice table has:

Date of invoice

Month of invoice

Year of invoice

Client no.

Invoice no.

Article id.

Sales price

Number of pieces

Client table has all the clients together with their contact data.

[avantime]

Ok guys, I am uploading a document picturing my dilemma.

The table on the left shows all my clients (1227 clients).

The table on the right shows clients that had at least one sale in the last two years (747 clients).

What I want is to have the table on the right show all 1227 clients (with 0 as Sales if the client has no sales in that period), no matter what year / month / day filter I apply.

Thank you!

[sunny_talwar · Accepted solution]

Try this:

Sum(Amount) + If(Len(Trim(Max({1} TOTAL <Year> Year))) > 0, Avg({1} TOTAL 0.0000000001))

[avantime]

You saved me from a lot of head scratching. Almost lost my hair because of it

Great solution, thank you!

[avantime]

I just noticed that filtering (year / month) does not fully work, it shows zeros instead of removing the column all together.

Any solution for that?

[sunny_talwar]

I thought you did not want it to be removed even when you select year/month/day

May be try this:

Sum(Amount) + If(Len(Trim(Max({1<Year = $::Year>} TOTAL <Year> Year))) > 0, Avg({1} TOTAL 0.0000000001))

or

Sum(Amount) + If(Len(Trim(Max(TOTAL <Year> Year))) > 0, Avg({1} TOTAL 0.0000000001))

[avantime]

No no, I want to filter the columns in the chart from a date point of view, but I want to see all clients, no matter of date selection, even if they have no turnover for the selected period.

The last solution you proposed unfortunately does not give the desired result because it filters clients.

Thank you very much for the time you put into this!

[sunny_talwar]

I will play around with it, but not able to fit both your requirements into one using a front end expression. You might be able to fix things in your script so that each client have at at least a 0 for each day/month/year so that it can be displayed on the chart.