Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
Showing results for 
Search instead for 
Did you mean: 
Contributor III
Contributor III

Bad ratio calculation due to value omission

ANSWERED HERE: Create new rows when there are no sales


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.

1 Reply

This might help you out:

Generating Missing Data In QlikView