Use a variable declared in SQL into Qlikview charts
Hello , I have created 2 Date variables Start date and End date and declared them in SQL script itself to limit the number of rows that are loaded.
So I do get correct data for tables when I simply add calendardate as dimension.Bbut when I try to use these variables individually in set analysis expressions, it doesn't seem to work.
e.g.: Sum ({<column_1={'value1'},calendardate ={"$(vStartnDate)"}>} Sales)
I am thinking if variables declared in SQL have different usage or syntax?
Excuse me if I am being foolishly specific, but I see that in your question you have mentioned the expression you use as "Sum ({<column_1={'value1'},calendardate ={"$(vStartnDate)"}>} Sales)"
and later you have mentioned that you have used it in script side as
"where t.calendate between $(vStartdate) and $(vEndDate);"
Hopefully it is not the unwanted 'n' in vStartnDate that makes all the trouble. It happened to me once. A small spelling mistake took a lot of debugging time. That is the reason I ask you this !
Sorry not able to reudce the data, if its not too much to ask, is it possible for you to create a mock app that mimicks what I am trying to do. Following is the sample sql statement which I am using:
Select t.column1,t.column2
from table t
where t.calendardate between (vStartDate) and (vEnddate)
and to narrow it down to simplest level let's assume I am makin a table that has Sales data for this Start Date
so the set analysis expression I am trying to use is : Sum ({<column_1={'value1'},calendardate ={"=date($(vStartDate))"}>} Sales)
Oh yes and an input box where i enter this date and reload.
But I just tried almost exactly the same in one of my current applications. The expression with the syntax you have in the opening question works fine:
sum({<Date={"$(VarDate)"}>} Price)
I have to notice that I have exactly the same date format for field Date and variable VarDate.
I agree with you the problem lies with my date formats.
When I input the date in variable as '19-jan-2015', the date conversion : ={"=date($(vStartnDate))"} does not seem to work. But if I enter my date as 1/19/2015 12:00:00 AM this conversion seems to work.
Is there any other date conversion so that the user can still input the date in '19-feb-2015' format in the variable and the date conversion in set analysis does not get broken.
That's the major reason I asked you to upload the app, otherwise we can only guess.
Your last statement implies that your calendar date is in fact a timestamp, i.e. with hh:mm:ss. Comparing timestamps is not a good idea, they seldom match. I recommend to load al your dates as dates without time, e.g.
Using the following formula: sum({<calendardate={'=Date(vStarDate)'>} Sales).
Now I do get values but looks like its adding up data for both start Date and End date and displaying summed up values and not for separating them for respective dates. So what am suspecting is it when we put values under ''(quotes) it considers them as text value and not variables.
Because I have a text box where i do =date(vStartDate) (in the form of a formula) it displays expected date from the variable; but when I use this between quotes it does not recognize the individual date.
