topic Re: date functionality in QlikView

date functionality

sakshikaul — Thu, 21 Feb 2019 06:09:59 GMT

Hi,

I have a query, I want to compare the sales for a current year with sales 10 days prior to current year

Here in the below image when user selects an event , corresponding date for that particular event and corresponding days for comparison

for eg as shown in below image users select event as Diwali, date as 10/10/2018

no of days for comparison =9

so Date selected for comparing is calculated based on following

Date selected by user(10/10/2018 )- no of days for comparison (9) so 'Date selected for comparison '=1/10/2018

so now I have to compare between the given range last year date ie 1/10/2018 and current year date 10/10/2018,

so this is working fine only if I am selecting no of days for comparison as 1,2,3,4,5,6,7,8,9 but if I am selecting no of comparison days as 10 then its not working because here date selected for comparison will be 30/09/2018 so this means here the month will be changed .

This is working fine

this is not working fine

Re: date functionality

pradosh_thakur — Thu, 21 Feb 2019 08:17:30 GMT

What is your expression?

Re: date functionality

sakshikaul — Thu, 21 Feb 2019 08:20:01 GMT

=sum({< Sold_Date = {">=$(From)<=$(To)"} >} quantity_sold)
- sum({< Sold_Date = {">=$(From)<=$(To)"} >} quantity_returns)

Re: date functionality

pradosh_thakur — Thu, 21 Feb 2019 08:35:16 GMT

Are from and to variables? If yes how do the look like?

Re: date functionality

shiveshsingh — Thu, 21 Feb 2019 09:00:28 GMT

It depends on your Model, this month change should not affect. Need to check the Variable value and expression.

Do you get any values if you check values for these two dates separately? for 31/09/2018 & 10/10/2018?

Re: date functionality

sakshikaul — Thu, 21 Feb 2019 09:20:43 GMT

From variable contains following value- =date(date(DaysOfComp),'D/M/YYYY')

To variable contains following value=date(lastdate,'D/M/YYYY')

Re: date functionality

sakshikaul — Thu, 21 Feb 2019 09:42:27 GMT

Yes if I am selecting date 31/09/2018 the corresponding values are shown.but if I am using a variable in the expression its not showing any result.

Even if I input No of comparison days as 11 or 12(ie =>10) no result is shown in the charts.

Re: date functionality

pradosh_thakur — Thu, 21 Feb 2019 09:48:06 GMT

how is No of comparison days related to the calculation . I am sorry bit we have limited view of data so you have to describe a bit.

Re: date functionality

shiveshsingh — Thu, 21 Feb 2019 09:52:10 GMT

How come you are getting values for 31st September?

secondly, are you evaluating this expression?

=sum({< Sold_Date = {">='30/09/2018'<='10/10/2018'"} >} quantity_sold)
- sum({< Sold_Date = {">='30/09/2018'<='10/10/2018'"} >} quantity_returns)

Re: date functionality

sakshikaul — Thu, 21 Feb 2019 09:57:00 GMT

Hi,

No. of comparison days means if user selects 9 as comparison days this means the user is interested in seeing sales 9 days prior to current date (that is selected by user under Date column)

therefore we can say if user selects date as 10/10/2018 and no of comparison days as 9, so this clearly means that user is interested in seeing the sales from 1/10/2018 till 10/10/2018

ie date - no of comparison days = last year date(after calculation )

10/10/2018 - 9 = 1/1/2018

Re: date functionality

pradosh_thakur — Thu, 21 Feb 2019 10:02:16 GMT

use the below set analysis in your expression

lets say your date field is datefield and v_days contain the no of days

{<datefield={"<=$(=max(datefield)) >=$(adddays(max(datefield),-$(v_days)))"}>}

Re: date functionality

sakshikaul — Thu, 21 Feb 2019 10:09:05 GMT

You mean this?

=sum({< Sold_Date = {"<=$(=max(Sold_date)) >=$(adddays(max(Sold_date),-$(compday)))"}>} quantity_sold)

Re: date functionality

shiveshsingh — Thu, 21 Feb 2019 10:14:25 GMT

sum({< Sold_Date = {">=$(=max(Date))<=$(=Date(max(date)-vNo))"} >} quantity_sold)
- sum({< Sold_Date = {">=$(=max(Date))<=$(=Date(max(date)-vNo))"} >}quantity_returns)

where vNO is the number of days entered.

Re: date functionality

sakshikaul — Thu, 21 Feb 2019 10:23:45 GMT

Hi,

I am using following expression but its giving me no result

sum({< Sold_Date = {">=$(=max(Date))<=$(=Date(max(date)- compday)"} >} quantity_sold)
- sum({< Sold_Date = {">=$(=max(Date))<=$(=Date(max(date)- compday)"} >}quantity_returns)

Re: date functionality

pradosh_thakur — Thu, 21 Feb 2019 10:27:18 GMT

sum({< Sold_Date = {"<=$(=max(Date))>=$(=Date(max(date)- compday)"} >} quantity_sold)
- sum({< Sold_Date = {"<=$(=max(Date))>=$(=Date(max(date)- compday)"} >}quantity_returns)

you and @shiveshsingh have written >= max date and <= maxdate-10 which is not what you want. ..Try the above. i have altered it.

Re: date functionality

sakshikaul — Thu, 21 Feb 2019 10:36:53 GMT

now I have tried what you said still not working

sum({< Sold_Date = {"<=$(=max(lastDate))>=$(=Date(max(lastDate)- compday)"} >} quantity_sold)
- sum({< Sold_Date = {"<=$(=max(lastDate))>=$(=Date(max(lastDate)- compday)"} >}quantity_returns)

Here lastDate is labelled(caption ) as Date in list box

Re: date functionality

pradosh_thakur — Thu, 21 Feb 2019 10:40:37 GMT

what do you see when you use =compday in a text box

and what do you see when you use =$(Date(max(lastDate)- compday) in a text box.

Re: date functionality

sakshikaul — Thu, 21 Feb 2019 10:45:49 GMT

Re: date functionality

pradosh_thakur — Thu, 21 Feb 2019 11:01:13 GMT

use thsi

Date(max(lastDate)- compday)

select the event please , then see

Re: date functionality

sakshikaul — Thu, 21 Feb 2019 11:06:45 GMT