Variable Issue in Set Analysis

[#1]

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

[#2]

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!

[lironbaram]

accroding to your script

your expression sholud be

sum({<Date={"$(=date(vTodayDate))"}>} Sales)

[#4]

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

[lironbaram]

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)

[#6]

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?

[lironbaram]

hei jenni

can you explain what you want to achive in the expression

that way i can help you better

[#8]

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

[#9]

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

[#10]

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

[ToniKautto]

@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.