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]

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)

[#3]

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

[johnw]

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.

[#5]

Thanks a lot John.

[#6]

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

[johnw]

I believe you just need single quotes around the date:

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

[#8]

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

[lironbaram]

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

[ToniKautto]

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