topic Re: Canonical Date Expression in QlikView

Canonical Date Expression

pkpandey — Mon, 26 Jan 2026 18:19:17 GMT

Hi All

I have two dates Start_Date and Completed_Date and I am using canonical date in my data model. My question is how to write a set expression to calculate amount for a selected period. Say user has selected period start as 05/05/2015 and period end as 15/05/2015.

What should be my set analysis to sum the amount based on the Start_Date and sum of Size based on the Completed_Date.

Re: Canonical Date Expression

Kushal_Chawda — Mon, 21 Mar 2016 07:09:29 GMT

Create the Flag while creating the Bridge Table for Canonical date

for eg.

Load Start_Date as Date,

'StartDate' as Flag

Concatenate

Load Completed_Date as Date,

'CompletedDate' as Flag

Now in Expression you can do something like

=sum({<Flag={'StartDate'},Date ={'$(=date(max(Date)))'}>}Amount)

=sum({<Flag={'CompletedDate'},Date ={'$(=date(max(Date)))'}>}Size)

Re: Canonical Date Expression

jagan — Mon, 21 Mar 2016 07:14:13 GMT

Hi,

Check this link

Canonical Date

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

Sum( {$<DateType={'Completed'}>} Size )

Hope this helps you.

Regards,

Jagan.

Re: Canonical Date Expression

pkpandey — Mon, 21 Mar 2016 07:28:28 GMT

Hi Jagan

You mean sum({<Flag={'StartDate'}, CanonicalDate={'>=date(max(CanonicalDate))<=date(max(CanonicalDate))'} Amount)

Re: Canonical Date Expression

Kushal_Chawda — Mon, 21 Mar 2016 07:33:37 GMT

Yes.Exactly

Re: Canonical Date Expression

jagan — Mon, 21 Mar 2016 07:45:14 GMT

Yes.. >=Min not Max.

sum({<Flag={'StartDate'}, CanonicalDate={'>=date(Min(CanonicalDate))<=date(max(CanonicalDate))'} Amount)

Regards,

Jagan.

Re: Canonical Date Expression

pkpandey — Mon, 21 Mar 2016 09:07:10 GMT

HI Jagan

Its not happening in my case.I am not getting the exact sum what I should get. The table I have is having a & b columns common in both and I have made a key and based on that I have made the master calendar. Though I have given the same expression as above.

Re: Canonical Date Expression

jagan — Mon, 21 Mar 2016 09:13:59 GMT

Can you attach sample file? Is Canonical date and date(Min(CanonicalDate)) are in same date format?

Regards,

Jagan.

Re: Canonical Date Expression

Kushal_Chawda — Mon, 21 Mar 2016 09:17:51 GMT

have you tries this

=sum({<Flag={'StartDate'},CanonicalDate={'$(=date(max(CanonicalDate)))'}>}Amount)

=sum({<Flag={'CompletedDate'},CanonicalDate={'$(=date(max(CanonicalDate)))'}>}Size)

Re: Canonical Date Expression

pkpandey — Mon, 21 Mar 2016 10:19:59 GMT

Hi Jagan

Yes my canonical date are in the same format as you have suggested. I am sending you my sample files.

regards

Re: Canonical Date Expression

pkpandey — Mon, 21 Mar 2016 10:22:17 GMT

On Mon, 21 Mar 2016 14:43:52 +0530 jagan mohan rao appala wrote

Canonical Date Expression

reply from jagan mohan rao appala in QlikView Deployment - View the full discussion

Can you attach sample file? Is Canonical date and date(Min(CanonicalDate)) are in same date format?

Regards,Jagan.

Reply to this message by replying to this email, or go to the message on Qlik Community

Start a new discussion in QlikView Deployment by email or at Qlik Community

Following Canonical Date Expression in these streams:

Inbox