link between two date

[Louveduval]

Hello , 

I'm sorry to bother you but I've a problem to join two date and I m very bad for that 
In my apps I've made that for my date  :

Load

"CLE_LIGNE_DISPO vente",

    "Date commande vente" as Date,

"Libelle ligne vente" as [Type date],

    CLE_article_depot

Resident LIGNE_VENTES

Where "Libelle ligne vente" = 'Commande vente';

Load

"CLE_LIGNE_DISPO vente",

    "Date BL vente" as Date,

"Libelle ligne vente" as [Type date],

    CLE_article_depot

Resident LIGNE_VENTES

Where "Libelle ligne vente" = 'BL vente';

Load

"CLE_LIGNE_DISPO vente",

    "Date facture vente" as Date,

"Libelle ligne vente" as [Type date],

    CLE_article_depot

Resident LIGNE_VENTES

Where "Libelle ligne vente" = 'Facture vente' ;

Left Join (FAIT)

Load

"CLE_LIGNE_DISPO vente",

"Prix unitaire vente",

    "Quantité vente",

    "Montant HT vente",

    "Marge ligne vente"

Resident LIGNE_VENTES;

It's work very well  but  : 
My line labels change so the product delivered becomes invoiced.

 I can no longer make the link between the delivery date and the sales line  
And I would like to compare the current month where there are invoices delivered and the month after where everything is invoiced.
So I would like to take delivery dates for everyone.
but I can't make the connection.

Delivery date; n°Invoice ; Invoice date , kind of date 

my measure for Y-1 :

Sum(

{<

    [Filtre CA vente] = {"Oui"}, 

        [Type date]={'BL vente'},

        AnnéeMois={"$(=MonthName(AddYears(RangeMin(Today(),max(Date)),-1)))"}

     >}

[Montant HT vente])

+

Sum( {<

    [Filtre CA vente] = {"Oui"}, 

        [Type date]={'Facture vente'},

        AnnéeMois={"$(=MonthName(AddYears(RangeMin(Today(),max(Date)),-1)))"}

     >}[Montant HT vente])

Can you help me ? 

[vincent_ardiet_]

Hi,
I don't see "AnnéeMois" in your loading script? 

[Louveduval]

Hi,

beacause it's on my calendar 

Temporaire:

Load

Min(Date) as minDate,

 // Max(Date) as maxDate

today() as maxDate

Resident FAIT;

LET vMinDate = Num(Peek('minDate',0,'Temporaire'));

LET vMaxDate = Num(Peek('maxDate',0,'Temporaire'));

Drop Table Temporaire;

// Création du Calendrier

Calendrier:

Load

*,

    MakeDate([Année comptable],$(vMoisDemarrage),$(vJourDemarrage)) as [Date début période comptable]

;

Load

    TempDate as Date,

    Year(TempDate) as Année,

    Month(TempDate) as Mois,

    Day(TempDate) as Jour,

    MonthName(TempDate) as AnnéeMois,

    WeekDay(TempDate)       as joursemaine,

    day(TempDate) & ' - ' & month(TempDate) as [Jour - mois],

    If(Num(Month(TempDate))<$(vMoisDemarrage),Year(TempDate)-1,Year(TempDate)) as [Année comptable];

//  WeekName(TempDate) as AnnéeSemaine;

//  Num(Month(TempDate)) as NumMois,

//  Week(TempDate) as Semaine,    

//  QuarterName(TempDate) as Trimestre,

//  WeekDay(TempDate) as JourSemaine,

//  Year(TempDate) & '-' & Month(TempDate)  as AnnéeMois,

//  Num(MonthName(TempDate)) as NumAnnéeMois,

//  Year(TempDate) & '-' & Week(TempDate) as AnnéeSemaine   

Load

    Date($(vMinDate) + IterNo() - 1) as TempDate

AutoGenerate (1)

while ($(vMinDate) + IterNo() - 1) <= $(vMaxDate);

best regard 

[vincent_ardiet_]

Ok, is this single part working?

Sum(

{<

    [Filtre CA vente] = {"Oui"}, 

        [Type date]={'BL vente'},

        AnnéeMois={"$(=MonthName(AddYears(RangeMin(Today(),max(Date)),-1)))"}

     >}

[Montant HT vente])

[Louveduval]

It's doesn't working due to the fact that  the product libellé produit change  from delivery at invoiced when it's payed . 
so the AnnéeMois it's no more linked with BL  but with Facture 

But we can always find the BL date with sale line but the link is broken  with the calendar 

[vincent_ardiet_]

So, you mean that in [LIGNE_VENTES], for a [CLE_LIGNE_DISPO vente], when the invoice has not been done,  [Libelle ligne vente] = 'BL vente' and once the invoice is emitted, it changes to 'Facture vente'? You don't have 2 rows one with 'BL vente' and another one with 'Facture vente'? Right?
And you cannot have a 'Facture vente' if no 'BL vente' has been done before?

If this is the case, in your script, maybe you can try to add this statement:

Load

"CLE_LIGNE_DISPO vente",

    "Date BL vente" as Date,

'BL vente' as [Type date],

    CLE_article_depot

Resident LIGNE_VENTES

Where "Libelle ligne vente" = 'Facture vente' ;