topic Re: Load clients with no turnover in QlikView

Load clients with no turnover

avantime — Wed, 25 Nov 2020 16:16:04 GMT

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!

Re: Load clients with no turnover

swuehl — Thu, 08 Sep 2016 17:51:35 GMT

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

Re: Load clients with no turnover

avantime — Thu, 08 Sep 2016 18:17:35 GMT

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!

Re: Load clients with no turnover

swuehl — Thu, 08 Sep 2016 18:24:50 GMT

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

Re: Load clients with no turnover

avantime — Thu, 08 Sep 2016 18:28:10 GMT

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

Re: Load clients with no turnover

avantime — Fri, 09 Sep 2016 07:13:14 GMT

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?

Re: Load clients with no turnover

avantime — Wed, 04 Jan 2017 15:01:05 GMT

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

Re: Load clients with no turnover

timsaddler — Wed, 04 Jan 2017 15:46:50 GMT

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

Re: Load clients with no turnover

avantime — Wed, 04 Jan 2017 19:29:08 GMT

Hi, thanks for the answer!

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

Re: Load clients with no turnover

sunny_talwar — Wed, 04 Jan 2017 19:37:00 GMT

How about this:

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

Re: Load clients with no turnover

johnw — Wed, 04 Jan 2017 19:52:54 GMT

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.

Re: Load clients with no turnover

avantime — Wed, 04 Jan 2017 20:09:05 GMT

Nope.. Not working, same result as before

Re: Load clients with no turnover

avantime — Thu, 05 Jan 2017 15:44:26 GMT

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.

Re: Load clients with no turnover

avantime — Fri, 06 Jan 2017 14:07:24 GMT

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!

Re: Load clients with no turnover

sunny_talwar — Sat, 07 Jan 2017 03:38:49 GMT

Try this:

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

Re: Load clients with no turnover

avantime — Sun, 08 Jan 2017 11:04:04 GMT

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

Great solution, thank you!

Re: Load clients with no turnover

avantime — Mon, 09 Jan 2017 10:15:39 GMT

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?

Re: Load clients with no turnover

sunny_talwar — Mon, 09 Jan 2017 10:21:44 GMT

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

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

Re: Load clients with no turnover

avantime — Mon, 09 Jan 2017 10:31:36 GMT

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!

Re: Load clients with no turnover

sunny_talwar — Mon, 09 Jan 2017 10:44:33 GMT

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.