topic Re: Restricting the data in line chart till the data availability in QlikView

Restricting the data in line chart till the data availability

qlikviewforum — Wed, 03 Dec 2014 04:07:59 GMT

Hi,

I have a line chart which has one dimension which is date field and we have 4 expressions. In that two of the expressions which the data is from one table has data till current date and in the rest of the expressions in which the data is coming from another table have data only till CurrentDate-2. Hence 2 of this lines doesn't goes till end and other lines which has data will current date goes till date. So the users either want to restrict the lines till the availablity of data all the 4 lines insted of two lines till the end and other two lines till CurrentDate-2.

Anyhelp of this?

Re: Restricting the data in line chart till the data availability

Anonymous — Wed, 03 Dec 2014 05:14:51 GMT

You can do this by using set analysis.

sum({<Date ={'<=$(=Date(max(Date)-2))'}>}Sales)

Regards,

Greeshma

Re: Restricting the data in line chart till the data availability

qlikviewforum — Wed, 03 Dec 2014 07:04:04 GMT

Hi Greeshma,

Thanks for your response but in my case CurrentDate-2 will not be the case all the times. More over in our set analysis we already have From and To date range. So how can I use the expression that you have suggested. Please help!

Re: Restricting the data in line chart till the data availability

Anonymous — Wed, 03 Dec 2014 07:21:08 GMT

You can create an alias field for date in both the tables and then define a variable as shown below

maxDate = date(rangemin(max(Date1),max(Date2)))

Use this variable in your set analysis expression. I hope this helps.

Re: Restricting the data in line chart till the data availability

qlikviewforum — Wed, 03 Dec 2014 08:06:48 GMT

Hi Greeshma,
I am creating the alias date field in both the tables as per your request. Could you please guide how can I use the variable as per your suggestion in the below expression.

All the expressions is something like the below one. So I am not sure where should I add the variable that you have suggested as per your request.

avg({<
AS_OF_DT = {'>$(=date($(zMaxPeriod)-$(VDaysForTrend))) <=$(=date($(zMaxPeriod)))'}
>}
Sales/1000000)

Please help!

Re: Restricting the data in line chart till the data availability

Anonymous — Wed, 03 Dec 2014 11:43:53 GMT

You can write your expression as follows

avg({<

AS_OF_DT = {'>$(=date($(maxDate)-$(VDaysForTrend))) <=$(=date($(maxDate)))'}

Sales/1000000)

where maxDate =rangemin(max(Date1),max(Date2)))

Re: Restricting the data in line chart till the data availability

qlikviewforum — Wed, 03 Dec 2014 12:17:31 GMT

Hi Greeshma,

Do you want me to use the Where clause in the both the table Load Statement? Am I correct?

Re: Restricting the data in line chart till the data availability

Anonymous — Thu, 04 Dec 2014 03:55:37 GMT

It is not a where clause. maxDate is a variable that needs to be created in the application. In the script you will only create aliases for the date field in both the tables. Then define the variable maxDate in the application. The fields Date1 and Date2 are the aliases created in the script. Once you have defined the variable give the set analysis expression for your line chart as

avg({<AS_OF_DT = {'>$(=date($(maxDate)-$(VDaysForTrend))) <=$(=date($(maxDate)))'} >}Sales/1000000)

Re: Restricting the data in line chart till the data availability

qlikviewforum — Thu, 04 Dec 2014 07:22:20 GMT

Hi Greeshma,

Thank you!

Do you want to create the fields like below?

Table1:

Load Date1,

Date1 as MaxDate1

;

Table2:

Load Date2,

Date2 as MaxDate2

;