Dimension as value in a set analysis expression

[#1]

Hello,

I've got the problem, that users would like to have values of dimensions in a set analysis expression, so that they can get a table like this:

Is there an opportunity to have an expression like this for the column "Sales NewCustomer":

Sum ({$<SalesDate={'>=NewCustomerFrom <=NewCustomerTill'} >} Sales)

Thanks!

Peggy

[#2]

PHoffmann,

Can you make ur requirement more clear.

Asper my understanding you can apply set in dimesion that will give u filtered result at dimesion and then expression value based on dimesion.

EXAMPLE.

=IF(
aggr(
rank(total
aggr( sum( {<Year=,YM={$(vLastMonth)},MonthName=>} Sales),CUSTOMER)
,0,1)
,CUSTOMER)
<=100
CUSTOMER))

This expression was applied at dimesion itself and filtered expression value .

[#3]

Hey Dushyant,

thanks for your answer!

I'm searching for a way to calculate the last column in the table above. The data model on which the table bases on is the following:

The blue fields are the dimensions (see also the table above).

How can I calculate the column "Sales NewCustomer" in the table above?

Hopefully the question is now clearer!?

Thanks,

Peggy

[#4]

Hi,

Sum ({$<SalesDate={'>=NewCustomerFrom <=NewCustomerTill'} >} Sales)

it is right, only format of all three SalesDate, NewCustomerFrom and NewCustomerTill should be same.

Reg,

Shubhu

[chris_johnson]

Hi,

It's just off the top of my head from what I've read on other posts but does this work?

Sum ({<Num(SalesDate)={">=Num(NewCustomerFrom) <= Num(NewCustomerTill)}>} Sales)

Also, I think QlikView is a bit 'funny' with dates in Set Analysis. I've seen people suggesting converting them to their number value in order to make them work.

Also to note, I removed the $ dollar sign from the set analysis as I guess you'll be working with the current set anyway. Feel free to put it back in if it makes a difference.

Regards,

Chris

[#6]

Hi,

the format is not the problem! In my formula QlikView does not recognize, that "NewCustomerFrom" and "NewCustomerTill" are names of dimensions. So how do I have to write the formula?

Sum ({$<SalesDate={'>=NewCustomerFrom <=NewCustomerTill'} >} Sales)

Is it possible at all, to have a different value for each row in a set analysis expression? Normally you can use static values or static variables for all rows.

Thanks for all answers!

Peggy

[chris_johnson]

Hi,

Can I ask why you think this is the reason? Set Analysis and date fields have always caused me issues and they will likely break anyway if the dates in the comparison are in different formats. Even trying to force the issue to set them to the same format hasn't worked for me in the past.

Did you try either of the suggestions? Was the result the same?

Regards,

Chris

[chris_johnson]

Oh, and to answer your question, you can use field names in set analysis much the way you are doing. That's why I'm unsure that the set analysis not recognising the fields is the issue.

Regards,

Chris

[#9]

Hello Chris,

if I use the expression

Sum ({<Num(SalesDate)={">=Num(NewCustomerFrom) <= Num(NewCustomerTill)}>} Sales)

QV shows me the first "num" in red letters and the last strings are all black and therefore not in the right colours.

I formated all columns in the script to the same format.

Normally when you write '>=NewCustomerFrom' QV interprets it as '>=NewCustomerFrom' and not as '>=01.05.2011'

Therefore I think I have to write some special strings, like when you use a variable (e.g. $(Variablename) )

Regards

Peggy

[chris_johnson]

Hmmm, it's a fiddly one.

I've been trying something similar with a report I'm doing to try to get the syntax right. How about:

=Sum( {$<SalesDate={">=$(num(NewCustomerFrom))<=$(num(NewCustomerTill))"}>} Sales)

Regards,

Chris