Count and AGGR using it as a measure as Dimension

[TheresaB_4]

Hi everyone,

My product is Gas and Electricity

I have customers that can have Gas or Electricity or both. I need to see how many customers have either both or just Gas or Electricity.
But each time I get this right it is a measure. I need to add the measure as a column(Dimension).
Or does anyone have another idea of getting the data? I would love to hear it.
I also was thinking doing this in the editor.

CustomerTable

CustomerNumber

ConnectionID

ProductTable

ProductID

ProductDescription

This is what I need in a pivot in Qlik

My measure
=if(count(
{<ProductDescription={'Electricity', 'Gas'}>}
Distinct ProductDescription)>1, 'Dual', 'Single')

[TheresaB_4]

I got the expression as a dimension but something is wrong because it gives me dashes

=aggr(
if(count(
{<ProductDescription={'Electricity', 'Gas'}>}
Distinct Productdescription)>1), 'Dual', 'Single')

[Anil_Babu_Samineni]

@TheresaB_4  I don't see that you are aggregating any dimension. Maybe this?

=if(count({<ProductDescription={'Electricity', 'Gas'}>} Distinct Productdescription)>1, 'Dual', 'Single')

[TheresaB_4]

I did make that measure, but I can not use in a pivot.

Maybe I am not asking the right question.
How do I count Distinct CustomerNumbers that has Both Electricity and Gas.

Maybe this is better

[Rohan · Accepted solution]

Hi Theresa,

have you used the Concat() Function ? I think you can use something like :

Aggr(Concat( distinct ProductDescription,','),CustomerNumber) as the dimension in the pivot. I think this will solve your issue.

Regards,

Rohan.

[Anil_Babu_Samineni]

@TheresaB_4 If that is as dimension

=if(Aggr(count({<ProductDescription={'Electricity', 'Gas'}>} Distinct Productdescription),<All your dimensions that is been used from chart>)>1, 'Dual', 'Single')

Otherwise, the question you asked already deserved with above set expression, since Distinct already there.

[TheresaB_4]

Awesome it works in the pivot, thanks so much Rohan.