topic Generate month & year on customer data in QlikView

Generate month & year on customer data

alex59800 — Fri, 01 Mar 2019 14:33:23 GMT

Hello i try to find a way to create Missing months in my table below

Month and year come from invoice date but as i have no invoice for example in februray, so i have no line.

I would like all month to appear for one year as i need to allocate fix amount on each month (250€ as below)

Is there any way through script to generate those missing month (2,3...) ? (i tried to left join a calendar with autogenerate date but not working)

Customer	Month	Turnover	Fix Amount to allocate on 12 months even if month does not appear (not invoiced)
040003	1	8 605.56 €	250
040003	4	21 472.48 €	250
040003	5	6 426.90 €	250
040003	6	19 796.40 €	250
040003	7	€ -556.80	250
040003	10	13 568.70 €	250
040003	12	9 677.98 €	250
040003	Total	78 991.22 €	1750

Re: Generate month & year on customer data

NhanNguyen — Fri, 01 Mar 2019 19:18:25 GMT

Try this:
LOAD RowNo() as Month,
Turnover,
250 as FixAmount
Autogenerate(12);

Re: Generate month & year on customer data

Vegar — Sat, 02 Mar 2019 22:52:38 GMT

I assume you do not have a Total row in your data set (if you do you will have to handle it additionally)

First generate the fixed value transactions with a loop.

For each vCustomer in fieldvaluelist('Customer')
For vMonth = 1 to 12
Transactions:
Load
'$(vCustomer)' as Customer,
$(vMonth) as Month,
250 as [Fix Amount]
Autogenerate 1;
Next
Next

Then left load your dimensions and turnover values.
Left join (Transactions)
LOAD
Customer,
Month,
Turnover
From DataSource;

Re: Generate month

alex59800 — Mon, 04 Mar 2019 13:43:46 GMT

Hello hanNguyen
Autogenerate (12,1) is not understood as a correct syntax

Alexandre DAUCHY
Responsable Contrôle de gestion Groupe
Brunel Chimie Dérivés
Fixe : +33 3 20 43 24 12
Port : +33 6 74 11 93 09
Abrégé : 6168
P avant d'imprimer, pensez à l'environnement

Re: Generate month

Vegar — Mon, 04 Mar 2019 13:50:54 GMT

The Autogenerate parameter needs to be a single positive integer.

Please check your code. I notice that @hanNguyen uses Autogenerate(12) not Autogenerate(12,1)