topic Re: Canonical date with 2 dates in QlikView

Canonical date with 2 dates

Anonymous — Mon, 15 Jun 2015 19:55:35 GMT

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

Re: Canonical date with 2 dates

vivek_niti — Wed, 17 Jun 2015 06:27:20 GMT

Hi,

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

Re: Canonical date with 2 dates

gautik92 — Wed, 17 Jun 2015 06:35:52 GMT

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)

Re: Canonical date with 2 dates

Anonymous — Thu, 18 Jun 2015 16:12:42 GMT

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.

Re: Canonical date with 2 dates

Anonymous — Thu, 18 Jun 2015 16:25:01 GMT

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.

Re: Canonical date with 2 dates

vivek_niti — Fri, 19 Jun 2015 03:31:11 GMT

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;

Re: Canonical date with 2 dates

Anonymous — Fri, 19 Jun 2015 17:50:15 GMT

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.