ValueList for join two demensions in one demension

[#1]

Hi all! i have a too big problem with my report.

This report should help my Customer (RETAIL CHAIN) to compare prices of chains vs prices of  competitors.

BUT! Customer compares their chains vs competitors and  their chains vs their chains .

They want to see in one object something like this:

Demension1	Expression1	Expression2, %
Chain1	$1
Competitor1	$2
Competitor2	$1
Competitor1	$3
Chain2	$1,5

I tried to use Valuelist() to join Chain_ID and Competitor_ID in one Demension, but I couldn’t return values in my Expressions.

I use for my Demension this:

Demension =  ValueList($(=Concat(DISTINCT Chr(39)&chain&Chr(39), ', ')), $(=Concat(DISTINCT Chr(39)&competitor&Chr(39), ', ')))

My Expression like this:

Expression=Count({<$(=$(vFieldName))={"$(=$(vFieldValue))"}>}DISTINCT sales_channel)

vFieldName = if(Match(ValueList($(=Concat(DISTINCT Chr(39)&chain&Chr(39), ',')), $(=Concat(DISTINCT Chr(39)&competitor&Chr(39), ','))), 'Chain1', 'Chain2', 'Chain3')>0, 'chain', 'competitor')

            
Due to this variable “vFieldName”  I get the fieldname for set analysis .

vFieldValue = SubField(Concat(DISTINCT Chr(39)&chain&Chr(39), ',')&','&Concat(DISTINCT Chr(39)&competitor&Chr(39), ','), ',', Match(ValueList($(=Concat(DISTINCT Chr(39)&chain&Chr(39), ',')), $(=Concat(DISTINCT Chr(39)&competitor&Chr(39), ','))),  $(vAvaliableValues)))

Due to the vatiable “vFieldValue” I get the field value for set analysis.

You can see my result:

I can to get FieldName and FieldValue, but I couldn't use it in my Expression.

PLEASE help me!

see att.

[jerem1234]

I would combine the dimension in the loadscript, and you could also get the values you need as well. It will make things much easier. I added this to your script:

New:

Load distinct chain as Dimension1,

count(distinct sales_channel) as value

Resident 'data'

group by chain;

New:

Load competitor as Dimension1,

count(distinct sales_channel) as value

Resident 'data'

group by competitor;

Please find attached dashboard.

Hope this helps!

[#3]

Kseniya,

please check and let me know if your problem is not similiar to this case:

Data model information

regards

Darek

[#4]

Hi Jerem!

Thanks for your answer, but i couldn't do it.

In first, I decided to create new field “STORE” including Chain_ID and Competitor_ID in my script.  But for one Chain I have about 10 competitors for one item_id, so, after join, I have 122 000 000  rows for 4 month. It is terrible. I should to load data for 1 years.

It is example my last structure and i have 122 000 000 rows:

ITEM_ID	CHAIN_ID	COMPETITOR_ID	PRICE_CHAIN	PRICE_COMPETITOR
200	1	2	$1	$0,89
200	1	3	$1	$0,75
200	1	4	$1	$0,91
200	1	5	$1	$1
200	2	2	$3	$3,1
200	2	3	$2	$1

I think i should use valuelist, but i can't to ger expression value:(

[#5]

Hi Darek! Thank you for this infotmation.

yes, i have the same problem, but after join  my tables  i have too many data, becouse for 1 chain for 1 item i have about 10-15 rows  (10 competitors for one item for one chain)

[#6]

I think i should use valuelist, but how???

[jerem1234]

If you want to use valuelist, you will have to change your expression up a bit. Since set analysis does not compute on a row to row basis, you'll need to use an if statement. I used this expression:

count(distinct if(chain=$(vTestDim2) or competitor=$(vTestDim2), sales_channel))

Did not use any other variable other than the one you used for valuelist. Not sure how this expression will do performance wise, if you have a lot of data.

I would still suggest the route of doing it in loadscript though.

Please find attached.

Hope this helps!

[#8]

Kseniya,

may you show entire data model?

And please write info what are data volumes.

regards

Darek