Restricting the data in line chart till the data availability

[qlikviewforum]

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?

[Anonymous]

You can do this by using set analysis.

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

Regards,

Greeshma

[qlikviewforum]

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!

[Anonymous]

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.

[qlikviewforum]

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!

[Anonymous]

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)))

[qlikviewforum]

Hi Greeshma,

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

[Anonymous]

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)

[qlikviewforum]

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

;