Solved: date duplication error - Qlik Community

Report Inappropriate Content · ‎2014-02-10

hi.

i am faced with the following.After having created a link table for my two dates

1. Invoice Date

2. Order Date

i am now faced with a serious issue.

the data is been duplicated to the highest date number

i cannot understand this behavior. All the keys are unique and the data is residing in different tables. I have attached my application for further analysis.

I appreciate the help.

Best

Brad

rwunderlich · ‎2014-02-11

I think your display is "correct" as is, that is, it displays what I would expect given your data model. You cannot mix InvoiceDate and Date together in the same chart. You are using Year/Month as selectors against the CommonCalendar, which would select a set of "Date". Date links to OrderId, and OrderId links to all InvoiceDate for that OrderId,

There is not enough granularity here using only OrderId, A selection in any calendar links via OrderId to all other dates associated with that same OrderId in the other calendar(s).

I assume what you want is to be able to select the common Year/Month and show Invoices sent that Month/Year? Remove the InvoiceDate field from your chart. Change the Dimension label to "Invoice Date" if you want.

-Rob

View solution in original post

chiru_thota · ‎2014-02-10

Hi,

I am not able to replicate the same issue if I add Date Type also.Can you try that once ?

Thanks,

Chiru

rwunderlich · ‎2014-02-11

I think your display is "correct" as is, that is, it displays what I would expect given your data model. You cannot mix InvoiceDate and Date together in the same chart. You are using Year/Month as selectors against the CommonCalendar, which would select a set of "Date". Date links to OrderId, and OrderId links to all InvoiceDate for that OrderId,

There is not enough granularity here using only OrderId, A selection in any calendar links via OrderId to all other dates associated with that same OrderId in the other calendar(s).

I assume what you want is to be able to select the common Year/Month and show Invoices sent that Month/Year? Remove the InvoiceDate field from your chart. Change the Dimension label to "Invoice Date" if you want.

-Rob

Report Inappropriate Content · ‎2014-02-13

Thanks Rob.

a quick one bout the common calender, do you have any code snippets for flags available ie. MTD_LY and LYTD for usage?

Best

Brad

rwunderlich · ‎2014-02-13

Hi Brad,

In the code you posted, a snippet like this would generate Current and Last YTD flags.

,InYearToDate([$(_field)], DateMax, 0) *-1 as [$(_prefix)CurYTD]

,InYearToDate([$(_field)], DateMax, -1) * -1 as [$(_prefix)LastYTD]

You would also need to add "DateMax" in the lower load as well.

I usually use Set Analysis for YTD/LYTD analysis instead of flags. I use the qlikview-components - A script library for Qlikview development Calendar routine which generates a pretty good setup for point in time analysis.

-Rob

http://mastersummit.com