Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Expression Comparision

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

2 Replies
MVP
MVP

Re: Expression Comparision

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

Luminary
Luminary

Re: Expression Comparision

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

Community Browser