topic Re: The Date Challenge! in QlikView

The Date Challenge!

Anonymous — Wed, 29 Mar 2017 11:44:25 GMT

Hi Experts!

I've a simple table with ID, OrderDate and Sales.

I also have another table which is not linked with the above table. This table has a list of dates like a calendar.

I want to be able to select any date from Calendar and then show all the OrderDate which >= Calendar Date and <= CalendarDate+15days.

For example if I select date 01/04/2016 I get this list:

Now what I actually want is a straight table that looks like this:

Is this something possible?

Re: The Date Challenge!

Anonymous — Wed, 29 Mar 2017 11:45:46 GMT

I'm attached the app and the data if anyone can have a look!

Re: The Date Challenge!

PrashantSangle — Wed, 29 Mar 2017 11:49:56 GMT

Hi,

what is your dimension??

use calendar date as your dimension.

Expression

sum(if(orderDate<=CalendarDate and orderDate>=CalendarDate+15,Sales)

Regards,

Prashant

Re: The Date Challenge!

karthiksrqv — Wed, 29 Mar 2017 11:59:18 GMT

Hi,

Try this as your calculated dimension:

=aggr(sum(distinct{<OrderDate={">=$(=max(Calendar))<=$(=max(Calendar)+15)"}>} OrderDate),OrderDate)

Do this after ensuring that both OrderDate and Calendar are in the number format during data-load. Manage the date-display format in the display objects.

Re: The Date Challenge!

sunny_talwar — Wed, 29 Mar 2017 12:03:44 GMT

May be you meant this (incorrect signs )

Sum(If(OrderDate >= Calendar and OrderDate <= (Calendar+15), Sales))

Re: The Date Challenge!

tresB — Wed, 29 Mar 2017 12:07:52 GMT

Like this?

Re: The Date Challenge!

PrashantSangle — Wed, 29 Mar 2017 12:12:08 GMT

thanks sunny

Re: The Date Challenge!

Anonymous — Wed, 29 Mar 2017 12:28:15 GMT

Yes this might work. I'll do further testing on this but is there anyway I can see the repeated Code and sales value as soon as they become available in the chart. like this?

Re: The Date Challenge!

Anonymous — Wed, 29 Mar 2017 12:29:13 GMT

ideally i want to have the calculated dimension rather than building this logic in expression.

Re: The Date Challenge!

Anonymous — Wed, 29 Mar 2017 12:30:25 GMT

Hi Karthik

I want this logic to be build in calculated dimension not at the expression level.

Re: The Date Challenge!

karthiksrqv — Wed, 29 Mar 2017 12:34:58 GMT

Hi Waqas,

Add the above expression as your calcuated dimension.

Re: The Date Challenge!

tresB — Wed, 29 Mar 2017 12:44:23 GMT

A code may come in different dates, and how would you want to deal such situation, show against only one date? if so which date?

Re: The Date Challenge!

sunny_talwar — Wed, 29 Mar 2017 12:46:38 GMT

I guess the OP wants, that once a Code is available on a day, it should be made available on every subsequent day after it was first available in that 15 day window

Re: The Date Challenge!

Anonymous — Wed, 29 Mar 2017 12:53:39 GMT

That;s exactly what I'm looking for Sunny!

Re: The Date Challenge!

tresB — Wed, 29 Mar 2017 12:59:41 GMT

You may create the data accordingly in the script using Peek()/Previous(). Check it here :How to populate a sparsely populated field