Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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')
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.
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')
@TheresaB_4 I don't see that you are aggregating any dimension. Maybe this?
=if(count({<ProductDescription={'Electricity', 'Gas'}>} Distinct Productdescription)>1, 'Dual', 'Single')
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
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.
@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.
Awesome it works in the pivot, thanks so much Rohan.