Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
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

waqqas2426
Contributor

Re: The Date Challenge!

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

Highlighted
waqqas2426
Contributor

Re: The Date Challenge!

Hi Karthik

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