10 Replies Latest reply: Mar 21, 2016 6:22 AM by Pradeep Kumar Pandey

# Canonical Date Expression

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

Create the Flag while creating the Bridge Table for Canonical date

for eg.

'StartDate' as Flag

Concatenate

'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

Hi Jagan

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

• ###### Re: Canonical Date Expression

Yes.Exactly

• ###### Re: Canonical Date Expression

Yes..  >=Min not Max.

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

Regards,

Jagan.

• ###### Re: Canonical Date Expression

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

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

Regards,

Jagan.

• ###### Re: Canonical Date Expression

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

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

© 1993-2016 QlikTech International AB  |

• ###### Re: Canonical Date Expression

have you tries this

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

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

• ###### Re: Canonical Date Expression

Hi,

Canonical Date

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

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

Hope this helps you.

Regards,

Jagan.