Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Set Analysis dynamically referencing to dimension value in pivot-table

Hi.

I have a question regarding a set-analysis: If I calculate a formula using set-analysis with a certain selection, is it possible to additionally select the dimension values (dynamically)?

It that's possible within the set-expression, how would the syntax be?

As an example I got the following table:

ProductType

Customer (cyclic group)

year

sold per year

shoes

test1

2007

350

2008

350

2009

350

test3

2008

350

test8

2007

350

test2

2008

350

total

0



Sold per year contains the following formula:

if(_Category='footwear' and Index('31,32,33,34',Right(CatID,2))>0,Count( distinct total {_Category = {"shoes","shoeslaces"},ProductType = {*}) sold_ID),sum(sold)))

What I need to achieve is that if certain IDs of our "footwear"-category apply, it should count the distinct IDs of another category, since I don't have a summable amount of sold units for these. Sadly, this formula doesn't take into consideration the dimensions in my pivot-table - leading to a amount of 350 wich is the number of sold units in total for ALL customers and years.

Strangely, in the total column nothing is being calculated?

Is there a way to dynamically tell the set-analysis formula to calculate for each line of the given table and select the corresponding values?

(e.g. ProductType = "shoes",Customer = "test1", year = "2007").

Tags (1)
2 Replies
Not applicable

Set Analysis dynamically referencing to dimension value in pivot-table

It would be easy to assist if you upload a QVW with some sample data.

Not applicable

Set Analysis dynamically referencing to dimension value in pivot-table

Okay, i've found a solution to this. Adding in the dimension/group names after the total modifier seems to do the trick.

if(_Category='footwear' and Index('31,32,33,34',Right(CatID,2))>0,Count( distinct total <CustomerGrp,Year> {_Category = {"shoes","shoeslaces"},ProductType = {*}) sold_ID),sum(sold)))

Community Browser