topic How do I reference two different states within the same expression? in QlikView

How do I reference two different states within the same expression?

Wed, 13 May 2015 23:02:31 GMT

I work for an eCommerce company and we are trying to clean up our product categories by seeing how many skus overlap between them.

To do this, I created a table with: (inherited state)

CategoryID

CategoryName

COUNT(DISTINCT(SkuID) as Sku Count

Then an associated categories table: (alternate state)

CategoryName

Count(DISTINCT{<SkuID = p({$} SkuID)>} SkuID) as Skus In Common

My next step is trying to see the proportion of Skus in Common compared to the original Sku Count from the first table, but I can't manage to make it work.

Any suggestions?

My alternate stats is called {AssociatedCategories}.

Re: How do I reference two different states within the same expression?

qlikviewwizard — Thu, 14 May 2015 09:02:29 GMT

Hi Try this.

Why Comparative Analysis

Re: How do I reference two different states within the same expression?

Thu, 14 May 2015 09:26:29 GMT

Hi Andrea,

The result of an Alternate state can be reffered to , inside a Set Expression,

For Eg. : here in your case(Not sure what your calculation is !)

Sum({[AssociatedCategories]<[Skus In Common]>}[SumField])

Hope it helps!

Cheers !

Re: How do I reference two different states within the same expression?

stigchel — Thu, 14 May 2015 09:29:35 GMT

Just as you reference the inherited state by using the $ sign, you can reference the alternate state by its name.

You can use this for sets as well as individual fields within the same set expression e.g.

Sum({AltSt1 <Region = $:: Region>} Sales)

Which means, take all selections from the Alternate state AltSt1 but take the selections for Region from the inherited state

Re: How do I reference two different states within the same expression?

Thu, 14 May 2015 16:12:19 GMT

I feel like this is the correct answer, but it's not working and I'm not sure why.

Keep in mind that I want to take the total count of skus in common, and divide it by the total number of skus possible.

So on the left hand side I have my inherited state - which shows the number of unique skus by category - and on the left I have another chart in an alternate state showing the number of skus in common with the left hand selection - and I want to get a proportion of the number of skus in common compared to the original sku count.

The proportion right now is the one you gave me - any suggestions?

Essentially I need to take Skus in Common (altstate) / Sku Count (inherited)

This is the expression I used for the above result.

[Skus in Common]/COUNT({AssociatedCategories <SkuID= $::SkuID>}DISTINCT SkuID)

Re: How do I reference two different states within the same expression?

stigchel — Fri, 15 May 2015 11:48:02 GMT

I'm not sure what is what in your above example and I'm not familiar with your data. However I think the problem is not with the states but with your dimensions and what you are dividing by. Can you try to divide

COUNT({AssociatedCategories <SkuID= $::SkuID>} TOTAL DISTINCT SkuID)

Re: How do I reference two different states within the same expression?

Fri, 15 May 2015 15:32:28 GMT

Hey!

So this is definitely working better, only it is giving me the total distinct skuID overall, and not the currently selected category of SkuID's (from the inherited state).

Re: How do I reference two different states within the same expression?

stigchel — Sat, 16 May 2015 08:51:17 GMT

What do you mean by " category of SkuID's", are there any selections in SkuID in the inherited state? Or do you have a category selected?

In the set expression you only reference SkuID in inherited state, only actual selections in this field will be reflected, so e.g. not possible values implied by another selction