topic Re: Master calendar issue in QlikView

Master calendar issue

vireshkolagimat — Thu, 30 Nov 2017 07:41:33 GMT

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

Re: Master calendar issue

kaushiknsolanki — Thu, 30 Nov 2017 07:55:45 GMT

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

Re: Master calendar issue

mdmukramali — Thu, 30 Nov 2017 08:03:28 GMT

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;

Re: Master calendar issue

vireshkolagimat — Thu, 30 Nov 2017 08:52:40 GMT

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

Re: Master calendar issue

mdmukramali — Thu, 30 Nov 2017 18:35:06 GMT

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,.

Re: Master calendar issue

vireshkolagimat — Tue, 30 Jan 2018 08:15:20 GMT

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,

Re: Master calendar issue

mdmukramali — Tue, 30 Jan 2018 16:06:45 GMT

Hi,

Can you attach the sample Data.

i will have a look.

Thanks,

Mukram

Re: Master calendar issue

vireshkolagimat — Thu, 01 Feb 2018 06:30:15 GMT

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,

Re: Master calendar issue

mdmukramali — Fri, 02 Feb 2018 15:56:59 GMT

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.

Re: Master calendar issue

vireshkolagimat — Fri, 02 Feb 2018 21:45:54 GMT

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,