topic Variable Issue in Set Analysis in QlikView

Variable Issue in Set Analysis

Thu, 20 Jan 2011 21:19:31 GMT

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

Variable Issue in Set Analysis

Thu, 20 Jan 2011 21:39:52 GMT

First of all, if you work with month, you need to create a Month field

= Month(CurrentDate)

. And your expression could be:

sum({$ <Month={$(=vLastMonth )}>} Sales)

Or if you need a range of dates for last month, it could be:


sum({$ <CurrentDate={"$(='>=' & Date('1/$(=vLastMonth)/2011' , 'D/M/YYYY') & '<' & AddMonths(Date('1/$(=vLastMonth)/2011' , 'D/M/YYYY') , 1))"}>} Sales)

Variable Issue in Set Analysis

Thu, 20 Jan 2011 22:08:54 GMT

Thanks Nick,

Can you please add another "pretend" field called Region and set it to say Manchester in the first set analysis expression that you used.

That would be very useful to me and could potentially solve my problem.

Thanks in advance

Variable Issue in Set Analysis

johnw — Fri, 21 Jan 2011 00:15:12 GMT

sum({<Region={'Manchester'},Month={$(=vLastMonth)}>} Sales)

There are a number of examples if you look for Set Analysis in the help file index. Not sure they do much with dollar sign expansion or variables, though.

Variable Issue in Set Analysis

Fri, 21 Jan 2011 00:23:14 GMT

Thanks a lot John.

Variable Issue in Set Analysis

Wed, 03 Aug 2011 15:06:52 GMT

Hi There!

I have the same Problem and I am lost .... Please help me!!! I did what John wrote, but it didn't work

I have a piechart! dimension is: product, and my formula for sale is: sum({<Date={$(=vTodayDate)}>} Sales)

it dosen't show the pie in my diagramm ...

but when I write: sum({<Date={$('30.06.2009')}>} Sales) --> its working.. but then I can't select other year to show the sale to other years!

In my Script, I wrote:

LET vTodayDate = num(MakeDate(2009,06,30));

and this:

Date(OrderDate) as Date, etc.

Please HELP ME.....

Re: Variable Issue in Set Analysis

johnw — Wed, 03 Aug 2011 20:34:24 GMT

I believe you just need single quotes around the date:

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

Variable Issue in Set Analysis

Thu, 04 Aug 2011 06:28:00 GMT

what does mean with single quotes?

sum({<Date={'$(=vTodayDate)'}>} Sales) --> it didn't work.... I can't understand why...

If I am doing:

sum({$<FinYear={$(=Max(FinYear))} >}(Sales)) --> it's working .. but it shows me just the actual year...

then I tried:

sum({$< FinMonth={$(=Max(FinMonth))},FinYear={$(=Max(FinYear))}>}(Sales)) --> Working also ....

then I tried:

sum({$< FinDay={$(=Max(FinDay))},FinMonth={$(=Max(FinMonth))},FinYear={$(=Max(FinYear))}>}(Sales)) --> it's not working, I dont know why???

Variable Issue in Set Analysis

lironbaram — Thu, 04 Aug 2011 06:39:41 GMT

hei

if vTodayDate is a varaible

then chcek that the result is in the same format as the Date field

when i use varaible i write in the expression

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

what is the formula in the varaible

Re: Variable Issue in Set Analysis

ToniKautto — Thu, 04 Aug 2011 06:42:35 GMT

You should not have to use the equal sign when you expand a variable. Assuming the variable is just a plain date, then you should be able to just expand it.

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

The quoting in general is required to make the quoted value a text string. Maybe the key to success would be to quote the FinDay part?

sum( { $< FinDay = { '$(=Max(FinDay))' } > }(Sales))

Variable Issue in Set Analysis

Thu, 04 Aug 2011 07:12:59 GMT

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!

Variable Issue in Set Analysis

lironbaram — Thu, 04 Aug 2011 07:17:31 GMT

accroding to your script

your expression sholud be

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

Variable Issue in Set Analysis

Thu, 04 Aug 2011 07:28:11 GMT

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

Variable Issue in Set Analysis

lironbaram — Thu, 04 Aug 2011 07:35:53 GMT

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)

Variable Issue in Set Analysis

Thu, 04 Aug 2011 07:42:40 GMT

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?

Variable Issue in Set Analysis

lironbaram — Thu, 04 Aug 2011 08:03:32 GMT

hei jenni

can you explain what you want to achive in the expression

that way i can help you better

Variable Issue in Set Analysis

Thu, 04 Aug 2011 08:29:19 GMT

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

Variable Issue in Set Analysis

Thu, 04 Aug 2011 08:58:13 GMT

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

Variable Issue in Set Analysis

Thu, 04 Aug 2011 12:57:22 GMT

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

Variable Issue in Set Analysis

ToniKautto — Thu, 04 Aug 2011 13:39:36 GMT

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