Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Support Case Portal has moved to Qlik Community! Read the FAQs to start exploring Support resources.
cancel
Showing results for 
Search instead for 
Did you mean: 
smilingjohn
Creator III
Creator III

Date

Hi All

after succefully runing the Master calendar , when i tried to check the date this is what  i find , I dont match with the existing date ,and also when i see in table view the subset ratio is only

PLz find the attachment .

1 Solution

Accepted Solutions
tamilarasu
Champion
Champion

Yes. Because I am looking the same excel file of yours from yesterday.

You can see your transaction date format is "DD-MM-YYYY HH:mm:SS TT" (Not DD-MM-YYYY HH:mm:SS ) you missed the AM/PM format. Change it like below

date(date#(TRANSACTION_DATE,'DD-MM-YYYY HH:mm:SS TT'),'DD-MM-YYYY')as Date  ,

Also I could see wrong date format for the column Period name. Change like below

date(date#(PERIOD_NAME,'MM-DD-YYYY'),'MM-YYYY TT')as Pmonth  ,

I guess you wrongly understood the date format by just seeing the cell. Check the formula bar everytime. You can see original date format.

Capture.PNG

View solution in original post

12 Replies
mightyqlikers
Creator III
Creator III

hi,

try date format like

date(date#(datefield,'DD-MM-YYYY HH:mm:SS'),'DD-MM-YYYY')

Regards
$@M

jsingh71
Partner
Partner

Try

Date(Date#(<Your Date Field>,'Your Date Field Format'), <'Desired Date Format'>)

Ex: Date(Date#(DateField, 'YYYY/MM/DD'), 'MM/DD/YYYY')

If possible share sample application.

tamilarasu
Champion
Champion

Hi John,

Subset Ratio shows you the percentage of values exists in the current table by comparing with the overall distinct values of that field.

Say for example Master calendar table has 100 date values (Date column) and Product table having only 50 date values (Date column) then the Subset Ratio for master calendar is 50% (50/100) and Subset Ratio for Product is 100% (100/100).


I could see your date field  formatted as DD/MM/YYYY hh:mm:ss (Differ from master calendar). Use proper date function that matches with your master calendar.


Hope this helps you.

smilingjohn
Creator III
Creator III
Author

HI Nagraj U r back , thanks for the explanation .

Please find my QVW file , u can see that in the tabular format the date is not matching , If u can please suggest me it will be great .

jonathandienst
Partner
Partner

Create a date field without the time component, or split it into a date part and a time part during load, like this:

     LOAD ....

          Date(Floor(Date)) As Date,

          Time(Frac(Date)) As Time,

This assumes that the field Date is correctly read as a numeric date value.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
smilingjohn
Creator III
Creator III
Author

Hi Jonathan , If u please look at my QVW file above it will be really helpfull for me ,

I tried ur option but yet iam getting the same thing , when i add all the fields in a table , the master calendar fields dont match they just keep below

tamilarasu
Champion
Champion

Yes. Because I am looking the same excel file of yours from yesterday.

You can see your transaction date format is "DD-MM-YYYY HH:mm:SS TT" (Not DD-MM-YYYY HH:mm:SS ) you missed the AM/PM format. Change it like below

date(date#(TRANSACTION_DATE,'DD-MM-YYYY HH:mm:SS TT'),'DD-MM-YYYY')as Date  ,

Also I could see wrong date format for the column Period name. Change like below

date(date#(PERIOD_NAME,'MM-DD-YYYY'),'MM-YYYY TT')as Pmonth  ,

I guess you wrongly understood the date format by just seeing the cell. Check the formula bar everytime. You can see original date format.

Capture.PNG

View solution in original post

smilingjohn
Creator III
Creator III
Author

Hi Nagraj

I followed the procedure u ask me to do , after reloading it , still when u add SBU , date, other fields from master calendar they dont match

jonathandienst
Partner
Partner

Your field TRANSACTION_DATE is already numeric, so instead of

date(date#(TRANSACTION_DATE,'DD-MM-YYYY HH:mm:SS'),'DD-MM-YYYY')as Date  ,

use this:

Date(Floor(TRANSACTION_DATE))

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein