topic Re: Dynamic Date Dimension? in QlikView

Dynamic Date Dimension?

ezanders — Thu, 06 Sep 2018 18:55:37 GMT

I work for an apparel company and I have a request from the Finance department for a QlikView report which should look like the table below. The issue I am having when I create this table is the first three expressions above the black line in the table use scheduled delivery date to calculate their totals, while the expressions below the black line use actual shipped date to calculate their totals.This is an issue for the date dimension placed at the top of the table as I can only pick one date for the dimension (scheduled delivery date or actual ship date). Is there a way to create dynamic date dimension that can switch between the two dates depending on the expression below or some way to create a static dimension which will display the months of a year yet allows me to call out the specific dates within the expressions? Also to be clear, there is no math that gets you from Booked Orders @ Net to Invoiced Shipped, they are independent of each other in this situation. This all stems from the requirement that these expressions need to appear in one table together. I hope I explained my issue well enough.

Jan-18 Feb-18 Mar-18

Booked Orders 756,000 800,000 785,000

(Front End Discounts) (6,000) (10,000) (7,000)

Booked Orders @ Net 750,000 790,000 778,000

Invoice Shipped 687,000 720,000 705,000

(Returns) (50,000) (54,000) (52,000)

(Credits) (100,000) (115,000) (107,000)

Net Sales 537,000 551,000 546,000

***Please keep in mind there is no need for the black line, I used this purely to explain the date issue.

Re: Dynamic Date Dimension?

marcus_sommer — Fri, 07 Sep 2018 07:09:37 GMT

You could try with a set analysis approach like this:

sum({< shippedMonth = {"=shippedMonth=deliveredMonth"}>} Value)

- Marcus

Re: Dynamic Date Dimension?

captain89 — Fri, 07 Sep 2018 12:50:20 GMT

tablex:

Code	Value	Order No.	Sched. Delivery Date	Invoice No.	Invoice Date
a001	100	O18001	07.08.2018	I18003	09.08.2018
a002	150	O18002	31.08.2018	I18004	02.09.2018
a003	500	O18003	25.09.2018	I18005	03.10.2018

Hi,

I think that you can solve the problem in the data model.

If you have only one table with invoices and orders you can split and concatenate them to get scheduled delivery date and actual shipped date in the same field.

Table:

load

'Booked Orders' as Description,

Code,

Value,

Order No.,

Sched. Delivery Date,

Sched. Delivery Date as MasterDate,

Invoice No.

Invoice Date

resident tablex;

concatenate

load

'Invoice Shipped' as Description,

Code,

Value,

Order No.,

Sched. Delivery Date,

Invoice No.

Invoice Date

Invoice Date as MasterDate

resident tablex;

drop tablex;

then you use MasterDate for your expression.

If you have orders and invoices in two difference tables you will create a MasterDate and another field where you put in the same field Invoice Value and Order Value....

Re: Dynamic Date Dimension?

rwunderlich — Fri, 07 Sep 2018 12:58:37 GMT

Similar to Matteo's suggestion, take a look at this tutorial which provides a pattern for handling multiple dates just like in your case.

Qlikview Cookbook: Tutorial - Using Common Date Dimensions http://qlikviewcookbook.com/recipes/download-info/tutorial-using-common-date-dimensions/

-Rob