topic Re: Calender Dates in QlikView

Calender Dates

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

Hi all

i have a calender object in my dashboard and i have 2 date fields like paid date and received date.

Now i am created a calender object based on paid date but user mentioned if he selects a start date in the object it should shows the data from 7 days before of his selection so i mentioned (paid date)-7 its working fine but he mentioned received date data should be in the range of his selection only

example:

if i select date range 7/10/2018 means paid date data should be 1/10/2018

but received date data in 7/10/2018 to current date only

Re: Calender Dates

manoj217 — Wed, 10 Oct 2018 10:09:48 GMT

One calendar dependent on two fields is possible

can we create a calender object on two variables for start date and end date ?

Re: Calender Dates

Anil_Babu_Samineni — Wed, 10 Oct 2018 10:58:28 GMT

Play around using MonthStart(DateField) from Object expression?

Re: Calender Dates

manoj217 — Wed, 10 Oct 2018 11:06:58 GMT

sorry it will not work my requirement is different

i have calender object if we select any date document will hold the data regarding those selections.

but if user select today as min date but i have to show before 7 days data it is working. it is created on one field payment date

but now i want to show the data on current selected date in another field received date

Re: Calender Dates

Anil_Babu_Samineni — Wed, 10 Oct 2018 11:15:27 GMT

Can you give sample data, Explain please for me?

Re: Calender Dates

manoj217 — Wed, 10 Oct 2018 11:25:57 GMT

paid date received date

10/12/2018 12/12/2018

13/12/2018 14/12/2018

14/12/2018 16/12/2018

15/12/2018 18/12/2018

17/12/2018 20/12/2018

20/12/2018 25/12/2018

if user selects a date 20/12/2018 but in document shows data from 14/12/2018 -----this data from paid date field

that means

14/12/2018 16/12/2018

15/12/2018 18/12/2018

17/12/2018 20/12/2018

20/12/2018 25/12/2018

this data will show in output but i want to show only

17/12/2018 20/12/2018

20/12/2018 25/12/2018

Re: Calender Dates

Anil_Babu_Samineni — Thu, 11 Oct 2018 00:22:11 GMT

You can try this way? Where as your problem comes thru Calendar. Better if you can share sample application

If([paid date] >= [receive date], [paid date])

Re: Calender Dates

uacg0009 — Thu, 11 Oct 2018 07:43:03 GMT

Hi manoj,

do you mean when you select 20/12/2018,

you want to show all the results that paid date >= 14/12/2018 and received date >= 20/12/2018?

if yes, I can handle this. if no, please help to clarify the logic.

Thanks.

Aiolos

Re: Calender Dates

bvssudhakar — Thu, 11 Oct 2018 07:49:56 GMT

Hi Manoj,

Can you share your qvd. so that i will try to solve your issue

Thanks & Regards,

Sudhakar

Re: Calender Dates

manoj217 — Thu, 11 Oct 2018 09:17:22 GMT

Yes Zhao your assumption is correct i want the same

Re: Calender Dates

qlikviewwizard — Thu, 11 Oct 2018 09:27:47 GMT

Hi,

Can you show your expected Sales in the attached output?

Data:

load *,DATE([paid date],'DD-MMM-YYYY') AS DateKey,DATE([paid date]-7) AS TEST;

LOAD DATE(DATE#([paid date],'DD/MM/YYYY')) as [paid date],DATE(DATE#([received date],'DD/MM/YYYY')) as [received date],Sales INLINE [

paid date,received date,Sales

10/12/2018,12/12/2018,12

13/12/2018,14/12/2018,10

14/12/2018,16/12/2018,45

15/12/2018,18/12/2018,76

17/12/2018,20/12/2018,90

20/12/2018,25/12/2018,80

];

Temp:

Load

min([paid date]) as minDate,

(max([paid date])) as maxDate

Resident Data;

Let varMinDate = Num(Peek('minDate', 0, 'Temp'));

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

DROP Table Temp;

TempCalendar:

LOAD

$(varMinDate) + Iterno()-1 As Num,

Date($(varMinDate) + IterNo() - 1) as TempDate

AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

MasterCalendar:

LOAD

DATE(TempDate,'DD-MMM-YYYY') AS Date,

DATE(TempDate,'DD-MMM-YYYY') AS DateKey,

date(monthstart(TempDate),'MMM-YY') as MonthYr,

week(TempDate) As Week,

Year(TempDate) As Year,

Month(TempDate) As Month,

Day(TempDate) As Day,

YeartoDate(TempDate)*-1 as CurYTDFlag,

YeartoDate(TempDate,-1)*-1 as LastYTDFlag,

// date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,

'Q'& ceil(month(TempDate)/3) as Quarter,

Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,

WeekDay(TempDate) as WeekDay

Resident TempCalendar

Order By TempDate ASC;

Drop Table TempCalendar;

Re: Calender Dates

uacg0009 — Thu, 11 Oct 2018 10:02:34 GMT

Hi manoj,

I think below is what you want, and please see my attachment.

Thanks

Aiolos

Re: Calender Dates

manoj217 — Thu, 11 Oct 2018 10:06:15 GMT

Thanks for your response i will check and let you know