topic Re: Expression Comparision in QlikView

Expression Comparision

Fri, 08 Jul 2011 18:19:01 GMT

Hi All,

I have this two expression in set analysis and I want to find those items for which

these two conditions matches.

Expression A.

count({$<FACT_TYPE={"Current"},LINE_ITEM_STATUS={'Design In', 'Design Win'},FLAG_HEALTH_REPORT={'1'}>}OppID)

Expression B.

count({$<FACT_TYPE={"Baseline"},LINE_ITEM_STATUS={'Pending'},DATE_SK={$(vBaselineDayKey)},FLAG_HEALTH_REPORT={1}>}OppID )

Expression A finds all the items that are in Design In or Design Win stage based on the current day data.

Expression B finds all the items that were in pending stage in the selected month.

I need to count all those items those were in Pending stage in that particular month, has now converted to Design In or Design Win stage as of now.

We are looking into two different snapshots of data. Any thing older than this week is saved and termed as FACT_TYPE= 'Baseline' and all data from this week is termed as FACT_TYPE='Current'.

Hope our Qlikview experts could help me please,

Thanks,

Bikash

Re: Expression Comparision

swuehl — Sat, 09 Jul 2011 00:05:09 GMT

Hi bikashdebnath,

I think you want the intersection of the two sets with regard to OppID.

Maybe something like:

count(distinct {< OppID = p({set1}) * p({set2}) >} OppID)

i.e.

count(distinct {< OppID = p({$<FACT_TYPE={"Current"},LINE_ITEM_STATUS={'Design In', 'Design Win'},FLAG_HEALTH_REPORT={'1'}>})*

p({$<FACT_TYPE={"Baseline"},LINE_ITEM_STATUS={'Pending'},DATE_SK={$(vBaselineDayKey)},FLAG_HEALTH_REPORT={1}>}) OppID)

which should set the intersection of the possible selections of your two sets for field OppID and count it.

(Haven't tried it in editor so maybe I missed a bracket).

Regards,

Stefan

Re: Expression Comparision

IAMDV — Sat, 09 Jul 2011 10:16:38 GMT

Hi Bikash,

Reading this statement from your question...

I need to count all those items those were in Pending stage in that particular month, has now converted to Design In or Design Win stage as of now.

I think you should be using Indirect Analysis within Set Analysis. I had similar requirement in the past and I got working with indirect Analysis. Below is the explanation, just in case you might need...

The functions p() and e() can be used to select the set of possible or excluded values within a field.

Example:

sum({$<CompanyName = p({$<CategoryName={'Baby Clothes'}>}

CompanyName) - p({$<CategoryName={'SportsWear'}>}

CompanyName)>} Sales)

Sales for Customers who purchased Baby Clothes but not Sportswear.The first set p({}) returns CompanyName that purchased Baby Clothes.The second set p({}) returns CompanyName that Purchased SportsWear.The Unary Operator "-" between the two gives a list of Customers (CompanyName)that belong to the first but not the other set.

However, if you can upload the QV document with some sample data. Then it will make it much simple to answer your question.

Cheers - DV