topic Re: Measuring Two dimensions with same values in App Development

Measuring Two dimensions with same values

sela — Mon, 15 Apr 2019 07:52:15 GMT

Hi my friends

I have a table witch include two dimensions with same values (Not specifically on the same rows ).

I need to count rows for every distinct value on this dimensions, Like the example below.

Any ideas?

Thanks, Sela

Re: Measuring Two dimensions with same values

miskinmaz — Mon, 15 Apr 2019 09:35:03 GMT

Hi,

You can try below code.

Load
Lead_id,
source A as source,
"A" as flag
from table

concatenate
Load
Lead_id,
source B as source,
"B" as flag
from table

and in expression you can try
count({<flag="A">}lead_id)

Re: Measuring Two dimensions with same values

sela — Mon, 15 Apr 2019 10:35:05 GMT

Is there any solution with on-the-fly calculation?

I already tried 'Value list' and Set Analysis, But it didn't worked....

Re: Measuring Two dimensions with same values

sunny_talwar — Mon, 15 Apr 2019 12:01:10 GMT

May be an expression like this

If(ValueList('Web', 'Phone', 'Email') = 'Web',
	Count(DISTINCT {<[Source A] = {'Web'}>} Lead_ID),
If(ValueList('Web', 'Phone', 'Email') = 'Phone',
	Count(DISTINCT {<[Source A] = {'Phone'}>} Lead_ID),
If(ValueList('Web', 'Phone', 'Email') = 'Email',
	Count(DISTINCT {<[Source A] = {'Email'}>} Lead_ID))))

and

If(ValueList('Web', 'Phone', 'Email') = 'Web',
	Count(DISTINCT {<[Source B] = {'Web'}>} Lead_ID),
If(ValueList('Web', 'Phone', 'Email') = 'Phone',
	Count(DISTINCT {<[Source B] = {'Phone'}>} Lead_ID),
If(ValueList('Web', 'Phone', 'Email') = 'Email',
	Count(DISTINCT {<[Source B] = {'Email'}>} Lead_ID))))

With this dimension

=ValueList('Web', 'Phone', 'Email')

Re: Measuring Two dimensions with same values

sela — Mon, 15 Apr 2019 14:28:08 GMT

Thank you for your ans 🙂

I have a long and dynamic list.

Because of that, I need some way to do it without "Ifs".

Is there any way to make it sort and dynamic?

Re: Measuring Two dimensions with same values

sunny_talwar — Mon, 15 Apr 2019 16:04:16 GMT

Nope, only way is to transform your data using CrossTable load in the script. Can't think of another easy way to do this