Problem with dates

[danimarc12]

Hello everyone!

I have a problem with my date fields in my app. 

this is the structure of my app, basically I have a date range in table "Budget" from "datainizio" to "datafine".

I have to display in my app a table which shows a value from table "Globale" and a value from table "Budget" in the same period but I don't know ho to link the date fields in "Budget" table to my Master Calendar.
If I use month(datainizio) I can see "budget"field (from table Budget) in each month but I don't see "molt"field (from table Globale). If I use month(data01) (which is "Mese" in my master calendar) I can see "molt"field in each month but I don't see "budget"field.

I don't know if I am clear with my deal, could anyone help me?

This is my script:

//** GLOBALE TABLE

Globale:
LOAD
idMarca &'_'& [pv] as [% Key marca_pv],
scontrino,
idMarca,
azienda,
nomeazienda,
tipodoc,
destipodoc,
numerodocumento,
data01,
[pv],
puntovendita,
codmailing,
ragsoc,
tipologiaCliente,
agente,
tipoAgente,
desagente,
segno,
molt,
clifor,
preventivo,
ordinefornitore,
tipostat,
idarticolo,
descrizione,
codesterno,
marca,
gruppo,
linea,
reparto,
codreparto,
famiglia,
codfamiglia,
idmagprovenienza,
idmagazzino,
magprovenienza,
magazzino,
qta,
prezzo,
sconto1,
sconto2,
sconto3,
importo,
costo,
provvigioni,
costoAnagrafico,
prezzolistino1,
prezzolistino2,
prezzolistino3,
prezzolistino4,
prezzolistino5,
prezzolistino6
FROM [lib://letturadati/Globale.qvd]
(qvd);

//** BUDGET TABLE

Budget:
LOAD
Budget.idMarca &'_'& Budget.pv as [% Key marca_pv],
Budget.idMarca,
ID,
Budget.tipo,
Budget.tipostat,
Budget.descrizione,
datainizio,
datafine,
Budget.pv,
idAgente,
Budget.codmailing,
budget,
peso,
budget*peso as budgetcalc
FROM [lib://letturadati/BudgetMarca.qvd]
(qvd);

//** CALENDAR

QuartersMap:
MAPPING LOAD
rowno() as Mese,
'T' & Ceil (rowno()/3) as Trimestre
AUTOGENERATE (12);

Temp:
Load
'01/01/2021' as minDate,
max(data01) as maxDate
Resident Globale;

Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
DROP Table Temp;

TempCalendar:
LOAD
$(varMinDate) + Iterno()-1 As Num,
Date($(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

MasterCalendar:
Load
TempDate AS data01,
week(TempDate) As Settimana,
Year(TempDate) As Anno,
Month(TempDate) As Mese,
Day(TempDate) As Giorno,
YeartoDate(TempDate)*-1 as CurYTDFlag,
YeartoDate(TempDate,-1)*-1 as LastYTDFlag,
inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,
date(monthstart(TempDate), 'MMM-YYYY') as MeseAnno,
ApplyMap('QuartersMap', month(TempDate), Null()) as Trimestre,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
WeekDay(TempDate) as WeekDay
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;

Thank to all!

[danimarc12]

I have seen something with the using of the Canonical Calendar but I'm not sure it can be the best solution. Did anyone had this kind of issue and found solution?

thank u all!

[MayilVahanan]

HI Dani,

You can create a key field with Date also in both global & budget.

idMarca &'_'& [pv]&'_'&datefield as [% Key marca_pv],

or

create the link table concept for your data model. 

[danimarc12]

Hi MayilVahanan, thanks for your answer.

I've concatenated also the datefield but it still not working... I think I have to create a calendar which matches the dates from the fields data01 (from Globale table) and datainizio (from Budget table).

Basically I want to display the sales, the budget and the differences.

Maybe I have to edit the Master Calendar? I really don't understand how to fix it.

[MayilVahanan]

Hi dani

Can you share the sample data and expected output ?

[danimarc12]

Yes, in attached you can find it!

My expected output is to see the "Venduto" column in the second image with the "Budget" column in the first image. It seems those measures can't recognize the corresponding month. If I use month(data01) I can see the correct "Venduto", If I use month(datainizio) I can see the correct "Budget" but I want to see both.

[MayilVahanan]

Hi Dani,

Can you send the data files to change the data model & verify from our end?

And also, expected output too..

[danimarc12]

I use a query to take datas from a database. I'm not selecting everything.

This is my query:

sub Estrazione

LIB CONNECT TO 'Appserver';
sql use cm70_gest;

Globale:
SELECT d.ID as scontrino,
mar.id as idMarca,
d.azienda,
az.ragsoc as nomeazienda,
d.tipodoc,
t.descrizione as destipodoc,
d.numerodocumento,
d.datadocumento as data01,
d.pv,
pv.Descrizione AS puntovendita,
d.codmailing,
m.ragsoc,
ts.descrizione as tipologiaCliente,
d.agente,
ag.tipo as tipoAgente,
ag.nome AS desagente,
t.segno,
if(t.segno='+',1,if(t.segno='-',-1,0)) AS molt,
t.clifor,
t.preventivo,
t.ordinefornitore,
t.tipo AS tipostat,
dd.idarticolo,
dd.descrizione,
mar.codesterno,
mar.marca,
mar.gruppo,
lin.descrizione AS linea,
r.descrizione AS reparto,
a.reparto AS codreparto,
f.descrizione AS famiglia,
a.famiglia AS codfamiglia,
d.magazzinoprovenienza AS idmagprovenienza,
dd.idmagazzino,
mgp.descrizione AS magprovenienza,
mg.descrizione AS magazzino,
dd.qta,
dd.prezzo,
dd.sconto1,
dd.sconto2,
dd.sconto3,
dd.importo,
dd.costorif AS costo,
dd.provvigioni,
getCostoNetto(a.costo,a.sc1,a.sc2,a.sc3,a.sc4) AS costoAnagrafico,
a.prezzolistino1,
a.prezzolistino2,
a.prezzolistino3,
a.prezzolistino4,
a.prezzolistino5,
a.prezzolistino6
FROM mag_documenti_dettagli dd
INNER JOIN mag_documenti d ON d.id=dd.iddocumento
LEFT JOIN mag_agenti ag ON d.agente=ag.id
INNER JOIN mag_tipologiedocumento t ON d.tipodoc=t.codice
LEFT JOIN mag_articoli a ON dd.idarticolo=a.id
LEFT JOIN mag_magazzini mg ON dd.idmagazzino=mg.id
LEFT JOIN mag_magazzini mgp ON d.magazzinoprovenienza=mgp.ID
LEFT JOIN mag_marche mar ON a.marca=mar.id
LEFT JOIN mag_puntivendita pv ON d.pv=pv.id
LEFT JOIN mag_linee lin ON a.linea=lin.id
LEFT JOIN mag_reparti r ON a.reparto=r.id
LEFT JOIN mag_famiglie f ON a.famiglia=f.id
LEFT JOIN cm70_wf.mailing m ON d.codmailing=m.codice
LEFT JOIN cm70_wf.tipologia_servizi ts ON m.tipologia=ts.codice
LEFT JOIN cm70_wf.aziende az ON d.azienda=az.id
WHERE t.tipo IN ('V','VI','F','D','FD');

store Globale into [lib://estrazionedati/Globale.qvd](qvd);

Qualify *;
Unqualify ID, datainizio, datafine, idAgente, budget, peso;

Budget:
SELECT ba.idMarca,
b.ID,
b.tipo,
b.tipostat,
b.descrizione,
bs.datainizio,
bs.datafine,
bn.pv,
bn.codagente AS idAgente,
bn.codmailing,
ba.importo as budget,
bs.peso/100 as peso
FROM mag_budget b
INNER JOIN mag_budget_split bs ON b.ID=bs.idbudget
INNER JOIN mag_budget_articoli ba ON b.ID=ba.idbudget
INNER JOIN mag_budget_nominativi bn ON b.ID=bn.idbudget;

Unqualify *;

store Budget into [lib://estrazionedati/BudgetMarca.qvd](qvd);

end sub

and then I use the .qvd files.

My expected output is to show in a Pivot Table the "Venduto" of the second image compared to the "Budget" of the first image (look at the post).

Thank you!