Master calendar issue

[vireshkolagimat]

Hi All,

I have fact table which stores the POS transaction data. I took the transaction date column which stores the data like  1/1/2017 8:31:00 AM.

I have created the master calendar using the same column to not getting the results properly.

If i sum the sdtolprc column i am getting the same amount as per backend database. But however if i make any selections in master calendar say month, the data won't show.

I have attached the QVW file for your reference and please help me to fix the issue.

Thank You.

R !egards,

Viresh

[kaushiknsolanki]

Try this,

sales:

LOAD shInvNo,

     TransactionDate as TransactionDate_Original,

     TransactionDate,

     shTotalPrice,

     shDiscAmt,

     shCashAmt,

     shCreditAmt,

     shRegNo,

     LocationCode,

     sdRegNo,

     sdItemCode,

     sdQtySold,

     sdSalePrc,

     sdTotalPrc,

     sdDiscAmt,

     sdMarkDown,

     sdLinkedQty,

     sdtranstype,

     sdItemCost,

     SynchDate,

     Modified_date

FROM

Sales.qvd

(qvd);

get_date:

load min(TransactionDate) as mindate,

    max(TransactionDate) as maxdate

    Resident sales;

let vmindate=Floor(peek('mindate',0,'get_date'));

let vmaxdate=Floor(peek('maxdate',0,'get_date'));

drop Table get_date;

temp_cal:

load $(vmindate)+IterNo()-1 as Num,

    date($(vmindate)+IterNo()-1 ) as temp_date

    AutoGenerate 1 while $(vmindate)+IterNo()-1 <= $(vmaxdate);

Master_cal:

load temp_date as TransactionDate,

   Year(temp_date) as Year,

   Month(temp_date) as Month,

   day(temp_date) as Day,

   hour(temp_date) as Hour

   Resident temp_cal;

drop Table temp_cal;

Regards,

Kaushik Solanki

[mdmukramali]

Hi,

sales:

LOAD shInvNo,

    Date(TransactionDate,'DD/MM/YYYY h:mm:ss[.fff] TT') as TransactionDate,

    Date(Floor(TransactionDate),'DD/MM/YYYY') as Transaction_Date,

    shTotalPrice,

    shDiscAmt,

    shCashAmt,

    shCreditAmt,

    shRegNo,

    LocationCode,

    sdRegNo,

    sdItemCode,

    sdQtySold,

    sdSalePrc,

    sdTotalPrc,

    sdDiscAmt,

    sdMarkDown,

    sdLinkedQty,

    sdtranstype,

    sdItemCost,

    SynchDate,

    Modified_date

FROM

(qvd);

//Master Calendar

get_date:

load min(Transaction_Date) as mindate,

    max(Transaction_Date) as maxdate

    Resident sales;

let vmindate=peek('mindate',0,'get_date');

let vmaxdate=peek('maxdate',0,'get_date');

drop Table get_date;

temp_cal:

load $(vmindate)+IterNo()-1 as Num,

    date($(vmindate)+IterNo()-1 ) as temp_date

    AutoGenerate 1 while $(vmindate)+IterNo()-1 <= $(vmaxdate);

Master_cal:

load temp_date as Transaction_Date,

  Year(temp_date) as Year,

  Month(temp_date) as Month,

  day(temp_date) as Day,

  hour(temp_date) as Hour

  Resident temp_cal;

drop Table temp_cal;

[vireshkolagimat]

Hi Mohammed,

yeah now the result looks fine. Currently it is reading data from 12:00:00 AM to 11:59:59 PM. Am i correct?

suppose if i query the data for 1 jan 2017, it will pull the data from  01 Jan 2017 12:00:00 AM  TO 01 Jan 2017 11:59:59 PM.

what if i want to read the data from 8:00:00 AM to 8:00:00 AM.

so for 01 Jan 2007 data will be from 01 Jan 2017 08:00:00 AM to 02 Jan 2017 08:00:00 AM. Is this possible?

Thank you.

Regards,

Viresh

[mdmukramali]

Hi,

Yes it's possible.

Work around is subtract 8 hours from TransactionDate .

like below

Date(Floor(timestamp(TransactionDate - maketime(8,0,0))),'DD/MM/YYYY')as Transaction_Date

Kindly find the attached sample application for your reference .

Thanks,

Mukram,.

[vireshkolagimat]

Hi, I tried the above script, it looks fine.

But the issue is there are some transactions which happened on 1st Jan 2018. When i look look for 2017 YTD sales(2017-01-01 8:00:00  AM To 2018-01-01 08:00:00 AM), these sales are not showing in 2017 YTD sales because i am reading the data from 8:00 AM to 8:00 AM. What ever the transactions which took place on 1st Jan 2018 8:00 AM should be part of the 31st Dec 2017.

Please let me know how to deal in this situation.

Thank you,

VK

[mdmukramali]

Hi,

Can you attach the sample Data.

i will have a look.

Thanks,

Mukram

[vireshkolagimat]

Hi, Please find the attached file.

The Net sales for Dec 2017 (2017-12-01 8:00:00 AM To 2018-01-01 8:00:00 AM) is 120113.

Thanks for the support.

Regards,

VK

[mdmukramali]

Hi Viresh,

the Net sales for Dec 2017 (From 2017-12-01 8:00:00 AM To 2018-01-01 7:59:59 AM).

Because we Added 8 hours from the Date so Till 7:59:00 AM the Date will be in PreviousData.

Thanks,

Mukram.

[vireshkolagimat]

Hi, What i was looking was, when ever i make selection in year say 2017, the rest 8 hours data for 2018-01-01 is not coming under 2017. If i want to see complete Dec 2017 data,i have to choose both 2017 and 2018.

Thanks for the clarifications.

Regards,

VK