topic Re: Dated in QlikView

Dated

smilingjohn — Tue, 01 Mar 2016 05:23:07 GMT

Hi All

I have a single table

In which there are 3 date fields which are

Entry Date

Collected Date

Sample Date

FInal Date

Among which i have used Entry date as Date for Master Calendar ,

Now my question is if i want to use other date fields also in master calendar how do i use it

Iam using the below script for master calendar for the Entry Date . Which is working perfect and giving

me month, year quarter and so on . How do i use the above trhee date fields within the same master calendar ?

I have renamed Entry Date as Date

MaxMin:

LOAD Max(Date) as MaxDate,

Min(Date) as MinDate

Resident ProductData;

SET vFiscalYearStartMonth = 4;

let vStartDate=Peek('MinDate');

let vEndDate=Peek('MaxDate');

FiscalCalendar:

LOAD

*,Dual('Q' & Ceil(FiscalMonth/3), Ceil(FiscalMonth/3)) AS FiscalQuarter, // Fiscal Calendar Quarter

Dual(Text(Date(MonthEnd(Date), 'MMM')), FiscalMonth) AS FiscalMonthName; // Fiscal Calendar Month Name

LOAD

Year(Date) AS Year, // Standard Calendar Year

Month(Date) AS Month, // Standard Calendar Month

Date(MonthEnd(Date), 'MMM') AS MonthName, // Standard Calendar Month Name

Dual('Q' & Ceil(Month(Date)/3), Ceil(Month(Date)/3)) AS Quarter, // Standard Calendar Quarter

Mod(Month(Date) - $(vFiscalYearStartMonth), 12)+1 AS FiscalMonth, // Fiscal Month

Right(YearName(Date,0,$(vFiscalYearStartMonth)),4)as FiscalYear,

YearName(Date, 0, $(vFiscalYearStartMonth)) AS FiscalYearr; // Fiscal Year

LOAD

Date($(vStartDate) + RangeSum(Peek('RowNum'), 1) - 1) AS Date,

RangeSum(Peek('RowNum'), 1) AS RowNum

AutoGenerate vEndDate - vStartDate + 1;

Thanks in advance

Re: Dated

jagan — Tue, 01 Mar 2016 05:29:49 GMT

Hi,

Can you attach some sample data and your expected result, so that it would be easier to understand and provide the solution.

Do you have multiple date fields in the same table?

Check this link

Canonical Date

Regards,

jagan.

Re: Dated

jagan — Tue, 01 Mar 2016 05:31:57 GMT

Also check this

Linking to two or more dates

Regards,

jagan.

Re: Dated

smilingjohn — Tue, 01 Mar 2016 05:36:29 GMT

I have a single table

In which there are 3 date fields which are

Entry Date

Collected Date

Sample Date

FInal Date

And Iam using Entry Date as Date in master calendar

Re: Dated

Digvijay_Singh — Tue, 01 Mar 2016 05:37:56 GMT

There are different approaches mentioned in below link by experts.

Master Calendar Date from Multiple dates in dataset

Re: Dated

jagan — Tue, 01 Mar 2016 05:41:26 GMT

Hi,

Check Canonical Date by HIC. You have to implement this in your Dashboard.

Regards,

Jagan.

Re: Dated

smilingjohn — Tue, 01 Mar 2016 05:43:42 GMT

Divijay

In my case all the 3 date fields are in a single table only

Re: Dated

jagan — Tue, 01 Mar 2016 05:46:58 GMT

Thats Ok, go through that link and implement the same. No matter if it is in the same table or different table.

DateBridge:

Load OrderLineID, [Entry Date] as CanonicalDate, 'Entry' as DateType

Resident OrderLines;

Load OrderLineID, [Collected Date] as CanonicalDate, 'Collected' as DateType

Resident OrderLines;

Load OrderLineID, [Sample Date] as CanonicalDate, 'Sample' as DateType

Resident OrderLines;

Load OrderLineID, [Final Date] as CanonicalDate, 'Final' as DateType

Resident OrderLines;

If you now want to make a chart comparing ordered and shipped amounts, all you need to do is to create it using a canonical calendar field as dimension, and two expressions that contain Set Analysis expressions:

Sum( {$<DateType={'Entry'}>} Amount )

Sum( {$<DateType={'Collected'}>} Amount )

Re: Dated

smilingjohn — Tue, 01 Mar 2016 05:52:20 GMT

Hi Jagan

PLease find the attachemnt of the sample QVW file ..

Here Iam considering two tables within the same table .

Re: Dated

smilingjohn — Tue, 01 Mar 2016 06:14:39 GMT

When i try to do this in my app

Its gving synthetic key .

Or can i have only Date field in resident load ? like this below ......

Load [Final Date] as CanonicalDate, 'Final' as Date

Resident Prodcuts;

Re: Dated

jagan — Tue, 01 Mar 2016 06:34:02 GMT

Rename Date AS DateType

Load [Final Date] as CanonicalDate, 'Final' as DateType

Resident Prodcuts;