Qlik Community

QlikView Deployment

Discussion Board for collaboration related to QlikView Deployment.

pkpandey
New Contributor II

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.

Labels (1)
10 Replies

Re: Canonical Date Expression

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)

MVP
MVP

Re: Canonical Date Expression

Hi,

Check this link

Canonical Date

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

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

Hope this helps you.

Regards,

Jagan.

pkpandey
New Contributor II

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

MVP
MVP

Re: Canonical Date Expression

Yes..  >=Min not Max.

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

Regards,

Jagan.

pkpandey
New Contributor II

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.

MVP
MVP

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

have you tries this

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

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

pkpandey
New Contributor II

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

Community Browser