Canonical date with 2 dates

[Anonymous]

I've read the QlikDesign Blogs by hic surrounding these topics, and i'm still at a total loss. I've tried this many different ways with an app that has just two dates (invoice created-on date and accounting document posting date). The issue is that invoicing a customer will create an accounting document in SAP, but the actual date when this accounting document is posted (actually effective) can vary. What i'm essentially trying to do is to get a sum of used and new products by customer (SoldTo) and also get the total revenue (Parts Revenue) of each of those customers for a certain area of the business (Parts). The new/used products sum is working and also changing according to selections made in my fiscal calendar (pertains to invoice date). But, i can't get the Parts Revenue to change (pertains to posting date) when making calendar selections. It stays the same... no matter what you select:

Is there any front-end expressions that i haven't thought of trying yet? Or, is this strictly a data model issue?

SoldTo is the dimension of my chart in the 1st screen snip. DATE field is going to connect my model to the Accounting Fiscal Calendar.

This is what the CanonicalCal Table script looks like right now:

As you all can see, i dropped fields from both of the main tables to avoid synthetic keys and/or circular references. However, this could be wrong and might be part of my problem.

When i make a Year selection, i'd simply like to see the revenue posted for that SoldTo (customer) and the number of cars invoiced to them at the same time.

This issue has plagued me for weeks and any input would be greatly, greatly appreciated.

Thanks a ton,

Ron

[vivek_niti]

Hi,

Are both the values of DateFlag available when you select a particular year?

[gautik92]

Create two different Calendars and use those calendar in the expression

lik this

=num(Sum({$< DisCALYear = {$(=Max(DisCALYear))},

BuilCALMonthID={$(=max({$< DisCALMonthID = {$(=Max(DisCALMonthID))} ,DisCALYear = {$(=Max(DisCALYear))} >}BuilCALMonthID) } > } TotalVolume),0.00)

[Anonymous]

The issue here is that i have a Ficscal Accounting Master Calendar that i'm plugging in to by DATE field to make fiscal year, quarter, and month selections built into a pre-built template.

[Anonymous]

Yes; However, when i select a particular fiscal accounting year (it works for standard calendar year), every year in the YearFlag field relates correctly except for 2014. When i select fiscal year 2014, YearFlag associates all years.

sorry for the late responses to both of you.

[vivek_niti]

Hi,

Instead of creating the yearflag while concatenating take a distinct resident load of the new table and then create the year flag. And don't create a DateFlag in the calendar create it in the transaction table.

CononicalCal_temp:

LOAD

  InvoiceRef AS Invoice,

  SoldTo,

  PostingDate AS Date

RESIDENT BSEGreload;

CONCATENATE

LOAD

  Invoice,

  InvoiceDate AS Date

RESIDENT VBRP;

CononicalCal:

LOAD DISTINCT

  Invoice,

  SoldTo,

  Date,

  Year(Date) AS YearFlag

RESIDENT CononicalCal_temp;

DROP TABLE CononicalCal_temp;

[Anonymous]

Hi Vivek,

Thanks for giving it a shot. I tried your solution and i'm getting the same result with the fiscal year 2014 selection. I'll post an update of where i'm at with this soon.