Canonical Date Expression

[pkpandey]

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.

[Kushal_Chawda]

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)

[jagan]

Hi,

Check this link

Canonical Date

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

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

Hope this helps you.

Regards,

Jagan.

[pkpandey]

Hi Jagan

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

[Kushal_Chawda]

Yes.Exactly

[jagan]

Yes..  >=Min not Max.

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

Regards,

Jagan.

[pkpandey]

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.

[jagan]

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

Regards,

Jagan.

[Kushal_Chawda]

have you tries this

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

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

[pkpandey]

Hi Jagan

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

regards