none
My quarterly month number is wrong RRS feed

  • Question

  • Hi everyone,

    I have a query that returns the information based on the field [Date de commande], for example, the field [Année], [Mois], [Trimestre] and [Nom du mois] are perfect, however, the month of the quarter (MoisDuTrimestre) is wrong, the third month is showing 0 instead of 3, so when I ask for a quarterly report, I have the 3rd month of the quarter showing zero sales.

    Here is the field in my query:

    Année: Année([Date de commande]) → ok

    Mois: Mois([Date de commande]) → ok

    Trimestre: PartDate("t";[Date de commande]) → ok

    Nom du mois: Format([Date de commande];"mmmm") → ok

    MoisDuTrimestre: (Mois([Date de commande]) Mod 3) → Show zero (0) see image below

    QUESTION: It would be perfect if the last field (MoisDuTrimestre) would show 3 instead of 0

    Thank you for your time

    Claude from Quebec, Canada


    Claude Larocque

    Tuesday, May 9, 2017 5:06 AM

Answers

  • MoisDuTrimestre: (Mois([Date de commande]) Mod 3) → Show zero (0) see image below

    Hi Claude,

    You can try:

    MoisDuTrimestre: (Mois([Date de commande]) - 1 Mod 3) + 1

    Imb.

    Tuesday, May 9, 2017 6:01 AM
  • When a new quarter begins, then it should restart at 1 meaning this is the first month of the quarter, then the second, then the third...

    Hi Claude,\

    I forgot some round brackets around the Monthnr and  - 1.

    So try:

    MoisDuTrimestre: ((Mois([Date de commande]) - 1) Mod 3) + 1

    This should do it.

    Imb.

    Tuesday, May 9, 2017 9:07 AM

All replies

  • MoisDuTrimestre: (Mois([Date de commande]) Mod 3) → Show zero (0) see image below

    Hi Claude,

    You can try:

    MoisDuTrimestre: (Mois([Date de commande]) - 1 Mod 3) + 1

    Imb.

    Tuesday, May 9, 2017 6:01 AM
  • Hi Imb

    Sorry but no luck here is what I have when using your formula...

    When a new quarter begins, then it should restart at 1 meaning this is the first month of the quarter, then the second, then the third...

    Thanks for your time

    Claude


    Claude Larocque

    Tuesday, May 9, 2017 7:51 AM
  • When a new quarter begins, then it should restart at 1 meaning this is the first month of the quarter, then the second, then the third...

    Hi Claude,\

    I forgot some round brackets around the Monthnr and  - 1.

    So try:

    MoisDuTrimestre: ((Mois([Date de commande]) - 1) Mod 3) + 1

    This should do it.

    Imb.

    Tuesday, May 9, 2017 9:07 AM
  • Thanks Imb, work at first try... you're great!

    Claude


    Claude Larocque

    Tuesday, May 9, 2017 3:31 PM