topic Re: Set analysis expression issues in QlikView

Set analysis expression issues

adnan_rafiq — Wed, 25 May 2016 02:29:04 GMT

Hi All Experts

Just wanted an idea on how to do this in set analysis.

i have a data of products some have USD and some have AUD in their Invoice Value. The USDtoAUD(vL.USDRate)rate is taken from user on front end.

What I want is, to be able to count the products whose converted price to AUD is greater than 100 and less than 1000.

The only issue is I am converting the price on front end. So i am making something of this sort which the expression editor shows errors on:

I am trying to convert the invoice price in set analysis.(red marked)

Count(

{<Invoice_Value/$(vL.USDRate)={'>=100<=1000'}

,Invoice_Currency={"USD"}>}

Prod_ID )

All help appreciated

Re: Set analysis expression issues

sunny_talwar — Wed, 25 May 2016 02:34:48 GMT

May be this:

Count({<Prod_ID = {"=Invoice_Value/$(vL.USDRate) >=100 and Invoice_Value/$(vL.USDRate) <= 1000"}, Invoice_Currency = {'USD'}>} Prod_ID)

Re: Set analysis expression issues

adnan_rafiq — Wed, 25 May 2016 02:39:46 GMT

Prod_ID is not the number that is being converted, here

the Prod_ID for those products need to be counted where, this expression :

Invoice_Value/$(vL.USDRate) is >=100 and <=1000

Re: Set analysis expression issues

sunny_talwar — Wed, 25 May 2016 02:42:40 GMT

I am just using the search string against Prod_ID. Have you given it a try?

Re: Set analysis expression issues

adnan_rafiq — Wed, 25 May 2016 02:46:57 GMT

Count({<Prod_ID = {"=Invoice_Value/$(vL.USDRate) >=100 and Invoice_Value/$(vL.USDRate) <= 1000"}, Invoice_Currency = {'USD'}>} Prod_ID)

this is returning 0.

As it should because Prod_ID has values like Prod A, Prod B etc not 123

Re: Set analysis expression issues

sunny_talwar — Wed, 25 May 2016 02:50:55 GMT

Look here for the syntax of search string from A Primer on Set Analysis

That is besides the point. Would you be able to tell me on what dimension are you checking =Invoice_Value/$(vL.USDRate) >=100 and Invoice_Value/$(vL.USDRate)<= 1000 against??

Re: Set analysis expression issues

adnan_rafiq — Wed, 25 May 2016 02:55:58 GMT

That is the thing

Invoice_Value/$(vL.USDRate)

has to become the dimenssion and be judged against upper(1000) and lower(100) limits

Re: Set analysis expression issues

sunny_talwar — Wed, 25 May 2016 03:00:20 GMT

Would you be able to share a sample with expected output?

Re: Set analysis expression issues

adnan_rafiq — Wed, 25 May 2016 04:12:29 GMT

I think you are right sunny

Thanks

So how does this start as a search string?