topic Re: Display data based on the calendar dates in QlikView

Display data based on the calendar dates

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

We have different dates used in the dashboard, but the requirement is to display data based on the calendar dates.

we are displaying 3 months, 6 months and YTD data and I'm facing issues while making selections.

In the 1st screenshot, for year 2017, John created 3 documents in last 6 months (June-Nov). When I select John (2nd screenshot), it is displaying that he created 5 documents in the last 6 months based on his latest document created date which was in September.

It has to display latest 6 months from calendar date and not from the document created date.

Can someone please help me fix this ASAP?

Thanks!

Re: Display data based on the calendar dates

johnca — Mon, 04 Dec 2017 15:53:31 GMT

In your calendar create another (master) date for all days, not just dates in your data. Then, use this in your expression.

//Concatenate(DOCUMENTS)

CALENDAR:

LOAD date(DATE_TIME_START,'MM-DD-YYYY') as DATE, // CALENDAR_DATE,

date(DATE_TIME_START,'MM-DD-YYYY') as MASTER_DATE,

month(date(DATE_TIME_START,'MMM-YY')) as CALENDAR_MONTH,

year(date(DATE_TIME_START,'YYYY')) as CALENDAR_YEAR

FROM $(vFldrProjQVDs)\CALENDAR.qvd (qvd);

Re: Display data based on the calendar dates

Colin-Albert — Mon, 04 Dec 2017 17:19:56 GMT

Have a look at this post on Canonical Dates Canonical Dateby Henric Cronstrom hic‌

and this one Linking to two or more dates‌ by Rob Wunderlich rwunderlich‌

These posts describes your situation where the data contains several different date fields but you need to make a single date selection.

Re: Display data based on the calendar dates

Anonymous — Mon, 04 Dec 2017 21:14:14 GMT

Thanks Albert and John.

Both Canonical Dates and Linking two or more dates didn't help.

Is it possible to lock the selection to show only latest 6 months when user selects 6 months view?

If so please let me know how. Thanks!

Re: Display data based on the calendar dates

rwunderlich — Sun, 17 Dec 2017 23:35:23 GMT

You may want to consider assigning the dates to groups as shown in this example:

Qlikview Cookbook: Date Grouping http://qlikviewcookbook.com/recipes/download-info/date-grouping/

-Rob