topic Dimension as value in a set analysis expression in QlikView

Dimension as value in a set analysis expression

Tue, 19 Apr 2011 10:23:32 GMT

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

Dimension as value in a set analysis expression

Tue, 19 Apr 2011 10:32:36 GMT

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 .

Re: AW:Re: Dimension as value in a set analysis expression

Tue, 19 Apr 2011 11:24:44 GMT

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

Dimension as value in a set analysis expression

Tue, 19 Apr 2011 11:41:09 GMT

Hi,

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

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

Reg,

Shubhu

Dimension as value in a set analysis expression

chris_johnson — Tue, 19 Apr 2011 11:43:29 GMT

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

AW:Re: Dimension as value in a set analysis expression

Tue, 19 Apr 2011 12:43:06 GMT

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

Dimension as value in a set analysis expression

chris_johnson — Tue, 19 Apr 2011 12:49:50 GMT

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

Dimension as value in a set analysis expression

chris_johnson — Tue, 19 Apr 2011 12:55:25 GMT

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

AW:Re: Dimension as value in a set analysis expression

Tue, 19 Apr 2011 13:02:41 GMT

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

Dimension as value in a set analysis expression

chris_johnson — Tue, 19 Apr 2011 13:24:43 GMT

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

Dimension as value in a set analysis expression

nathanfurby — Tue, 19 Apr 2011 13:42:23 GMT

This syntax should work:

sum({<Date={">=$(From) <=$(To)"}>} Value)

Of course there may be format issues - hard to test without the sample data.

Dimension as value in a set analysis expression

rbecher — Tue, 19 Apr 2011 13:49:50 GMT

I think this should be quoted:

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

- Ralf

AW:Re: Dimension as value in a set analysis expression

Tue, 19 Apr 2011 13:56:34 GMT

Hi Chris,

thank you for your answers.

If I use your formular QlikView interprets it like this

I think its good to have an example QVW-file for this tricky problem, but I can't load it.

Regards

Peggy

AW:Re: Dimension as value in a set analysis expression

Tue, 19 Apr 2011 14:11:49 GMT

Thanks Ralf,

good idea, but in my example qvw it doesn't work...

Regards

Peggy

AW:Re: Dimension as value in a set analysis expression

Tue, 19 Apr 2011 14:13:49 GMT

May be it doesn't work with QV 9 Version?

Dimension as value in a set analysis expression

chris_johnson — Tue, 19 Apr 2011 14:25:25 GMT

Hi,

Yes, I have found it will evaluate to blank values for the dates in the heading. I guess because there are multiple rows with different values in for the from and to dates it will not be able to show you. This is just the heading though, as long as you get the correct values it should be ok.

What do the values in your rows look like?

Regards,

Chris

AW:Re: Dimension as value in a set analysis expression

Tue, 19 Apr 2011 14:31:41 GMT

Hi Chris,

I have posted the example...

Can you have a look at it?

Thanks

AW:Re: Dimension as value in a set analysis expression

rbecher — Tue, 19 Apr 2011 14:34:50 GMT

This works on Version 10:

=Sum( if(SalesDate>=NewCustomerFrom AND SalesDate<=NewCustomerTill, Sales))

- Ralf

AW:Re: AW:Re: Dimension as value in a set analysis expression

Tue, 19 Apr 2011 14:40:01 GMT

Hallo Ralf,

danke! Das ist es!

Mal sehen ob ich das in meine komplexe Formel einbauen kann!

Viele Grüße

Peggy

Dimension as value in a set analysis expression

chris_johnson — Tue, 19 Apr 2011 14:46:00 GMT

Hi,

I was actully just about to suggest that (honest), you're too quick for me Ralf!

Glad it got sorted in the end though.

Regards,

Chris