Bad ratio calculation due to value omission

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

ANSWERED HERE: Create new rows when there are no sales

Hello,

I am pretty new in the Qlik world and I have a QlikView program, wich gets two datebases from SQL server. The databases are from a car dealer.

The 1º has 3 columns: Cars sold, year and month.

The 2º has 4 columns: price of accessories sold, accessory brand, year and month.

So year and month are linked in QlikView.

The problem comes when I try to make a ratio of price/cars sold in a bar chart (Expression: sum(PNC)/sum([cars sold]). When a brand hasn't sold any accessory in a month, in the second data base there isn't rows showing that the sales were 0 €, so qlik just ignores the cars that have been sold in that month. But I need to include them for the ratio to work correctly, I need to add 0 accesories sold per 34 cars sold (for example).

I thought some solutions:

To add the rows manually. But there are like 30.000 so it would take me a lot of time.

To analyse the datebase inside the script editor. If there's not a sale in a month, to create a new row with the accessory brand name. the month, the year and the 0 sale. The problem here is that I don't know how to program in Qlik.

So, is there any option in Qlikview that does this atomatically? If not, can someone help me with my second solution?

Thanks a lot.

Re: Bad ratio calculation due to value omission

adamdavi3s — Tue, 10 Jan 2017 12:32:16 GMT

This might help you out:

Generating Missing Data In QlikView

topic Bad ratio calculation due to value omission in QlikView

Bad ratio calculation due to value omission

Re: Bad ratio calculation due to value omission