Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

waqqas2426
Contributor

The Date Challenge!

Hi Experts!

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

tab1.PNG

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

tab2.PNG

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:

tab3.PNG

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

tab4.PNG

Is this something possible?

Tags (2)
14 Replies
waqqas2426
Contributor

Re: The Date Challenge!

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

Re: The Date Challenge!

Hi,

what is your dimension??

use calendar date as your dimension.

Expression

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

Regards,

Prashant

Great dreamer's dreams never fulfilled, they are always transcended.
Partner
Partner

Re: The Date Challenge!

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!

May be you meant this (incorrect signs )

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

Capture.PNG

MVP
MVP

Re: The Date Challenge!

Like this?

Capture.PNG

Re: The Date Challenge!

thanks sunny

Great dreamer's dreams never fulfilled, they are always transcended.
waqqas2426
Contributor

Re: The Date Challenge!

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?

tab5.PNG

Highlighted
waqqas2426
Contributor

Re: The Date Challenge!

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

waqqas2426
Contributor

Re: The Date Challenge!

Hi Karthik

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