Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi guys,
I am very new to QlikView and I am trying to work out how to use a variable in a set analysis expression.
I have tried doing it, but it is not giving me the right results. I believe I might be doing something wrong when I reference the variable in the set analysis.
Please can someone kindly help me write out a simple set analysis expression using one variable and two fields.
Let's assume the variable is called vLastMonth and the field name is called CurrentDate.
Thanks in advance
that is my varialbe:
LET vTodayDate = num(MakeDate(2009,06,30));
and my Date is:
Date(OrderDate) as Date
that is for FinDay:
that is the code for my Calendar:
FiscalCalendar:
LOAD
*,
FinYear & '-' & Quarter as FinYearQtr,
FinMonth &'-'&FinYear as FinMonthYear,
Year & '-' & Quarter as YearQtr,
if(FinYear = $(vCurrentYear), 1, 0) as CY_Flag,// Current Year Flag
if(FinYear = $(vLastYear), 1, 0) as LY_Flag // Last Year Flag
;
LOAD
OrderDate,// is from the Database
Year(OrderDate) as Year,
Month(OrderDate) as Month,
Week(OrderDate) as Week,
Day(OrderDate) as Day,
Date(OrderDate) as Date,
'Q' & Ceil(Month(OrderDate)/3) as Quarter, // Calendar-Quarter
'Q' & Ceil(Month(addmonths(OrderDate,-6))/3) as FinQuarter, // Fiscal-Quarter
Date(Monthstart(OrderDate),'MMM-YYYY') as YearMonth,
if(num(month(OrderDate)) <= 6, month(OrderDate) + 6, month(OrderDate)-6) as FinMonth,
if(num(month(OrderDate)) <= 6, Year(OrderDate) , Year(OrderDate)+1) as FinYear,
if(num(month(OrderDate)) <= 6, Day(OrderDate) , Day(OrderDate)) as FinDay
RESIDENT SalesOrder;
I am a new User in QlikView... Sorry, that I can't understand everything what u meant... Therefore I pasted some codes, what I did....
I tried with the formula what u posted.. nothing worked.... by the way thx for helping me!
accroding to your script
your expression sholud be
sum({<Date={"$(=date(vTodayDate))"}>} Sales)
Thank you so MUCH!!!! It is working now... Thanks a lot!!! I am so happy now 🙂
but I have one more question: now it's showing me of the date 30.06.2009, but when I am selecting another year for example, then it wont be calculating for other dates...
I know why, but how can I do that it will also calculating for other years, month, and dates???
hei
for what Date do you want it to calculte
let say you want it for the maxium date
the the varaible vTodayDate should be
=max(Date)
Okay then I will try with max(Date), that means if I am using the max() then I can also selecting other dates and it will calculating?
hei jenni
can you explain what you want to achive in the expression
that way i can help you better
sorry for my bad explaination... Enlish is not my native language ....
I want in my forumula: if there is no selection, then show me of the date 30.06.2009 (that one is my current Date in my application)
But when the user will selecting the year or month or day or another Date for example :22.05.2007, then it should also show of this date.
Without selecting it should show of the 30.06.2009. but the user can also selecting other dates...
I did this:
sum({$<FinMonth={$(=Max(FinMonth))},FinYear={$(=Max(FinYear))},FinDay={$(vCurrentDay)}>}(Sales))
it’s working, but I can selecting just the year, and the month, but If I am selecting a Date (22.05.2007) or a Day then it wont be calculating.
Thank you so much for ur helping, I was yesterday really lost!!!
With this formula: sum({$< Date={'$(=Max(Date))'} > }(Sales))
I can just selection in the Field “Date”
On my app, I have more Fields: Year, Month, Quarter, Day, and Date
And I want that I can selecting all of them, Do you understand what I mean? without selecting, it should show me the date of 30.06.2009 because that date is my current date...
Hallo me again, what you mean with the :
the the varaible vTodayDate should be
=max(Date)
I tried everything, but it still not working correct
@Jenni; One reflection after looking at the previous code and the solution. I do not really see why you set the variable to date and then convert it to a number, if you then use it later on to compare with another Date. To me it would be better to avoid converting data and work with the dates in the Date format straight away.
Consider that Date is a dual data type where there is a textual part that presents the readable content (eg 2011-05-28) and the underlaying numeric value is the number of days from 31/12/1899. If you set a variable to be a Date, then you can use this directly in calculations. Date - 23 would for example change the Date value 23 days back in time.