15 Replies Latest reply: Dec 18, 2017 12:50 AM by Pooja Goswami

# Create expression as per excel in qliksense

Hello,

I have attached a excel in attachments.

My requirement is that i have created a pivot in excel (Sheet 4 tab)

I have taken field location as filter.

Customer in rows.

Sector in columns

Count of Empid as values.

Now I have created a new column besides grand total column named as count.

The expression to calculate column COUNT is Countif(range, ">0") where for range i selected from PQR to ABC .

Now after calculating count column i put  filter on it with value greater than 1 as shown below

After putting this filter value as greater than 1 i got the total count as 2 (fake and shipment).

I want to implement an expression in qliksense in order to get this count.

Kindly help.

• ###### Re: Create expression as per excel in qliksense

Hi Pooja,

In order to do that, please do the following:

Create a filter pane or listbox with Location

The create a pivot table with Customer as Row Dimension and Sector as Column Dimension

Then use the following as your measure:

=IF(SecondaryDimensionality()=1,Count([EMP ID]),IF(Dimensionality()=0,COUNT(DISTINCT Sector),AGGR(COUNT(DISTINCT Sector),Customer)))

Then enable Totals for Sector and for Customer,

It should give you this:

See that in my count I have 6 and this is because in Excel you're not including Sector ABC, is that on purpose? of was just missed?

I hope this helps

Luis

• ###### Re: Create expression as per excel in qliksense

Yeah I was suppose to include ABC as well. I forgot to do that.

But what I need is that I need an expression for calculating that  count in one go...to show it as one parameter in other table.

• ###### Re: Create expression as per excel in qliksense

Then you may need this one?

=AGGR(COUNT(DISTINCT Sector),Customer)

• ###### Re: Create expression as per excel in qliksense

its not working..i already tried that...

I have created this expression so far

if(sum(if(aggr(Count({\$<[Customer Name]={'abc factory','fake','real'}Sector={'ABC','PQR','DEF'}>}[Emp ID]),[Customer Name],Sector)>0,1,0))>3,1,0)

Now I want to use this expression inside sum function in order to sum the out put of this expression.

But its showing nested aggr not allowed error.

• ###### Re: Create expression as per excel in qliksense

Hi Pooja,

You showed very good explanations in this post. Can you please explain again the final outcome you want? I'm not confused about what you really want

• ###### Re: Create expression as per excel in qliksense

I wanted to create an expression just like I did in excel to get that count 2 after applying all the logics in excel like taking count(empid) then checking if count(empid)> 0 then 1 else 0 then taking the sum of this count(empid) i.e

sum(if(aggr(count(EMPLID),CUSTOMER_NAME,sector)>0,1,0))

Now then comparing this whole expression if >3 then 1 else 0 i.e.

if(sum(if(aggr(count(EMPLID),CUSTOMER_NAME,Sector)>0,1,0))>3,1,0)

Now I wanted to use this entire expression in sum function in order to sum the individual count of true condition which would come out to be 1 and would give me 2 as total count as I showed in images .

• ###### Re: Create expression as per excel in qliksense

Hi, without the formulas, can you please describe the outcome maybe in a screenshot?

Are you after a KPI? What is the expected result according to the data provided?

Are you after a table? What is the expected result according to the data provided?

I have provided all the numbers you have shown so I really don't understand what you're after.

if it's about a nested Aggr, you already created a post for that.

In this post we're trying to solve the issue you presented

Cheers,

Luis

• ###### Re: Create expression as per excel in qliksense

I have a pivot table which has a parameter delivery and I need to show this number corresponding to that parameter.

like

I need to show number corresponding to this parameter.

so I need to create an expression to get this count though count in our case has to come as 2 if taking grater than 1.

• ###### Re: Create expression as per excel in qliksense

=SUM(IF(AGGR(COUNT(DISTINCT Sector),Customer)>1,1,0))

• ###### Re: Create expression as per excel in qliksense

can u plss share the qvf.

• ###### Re: Create expression as per excel in qliksense

this far is quite right. Now I need to compare this expression as > 3 ie.

if(SUM(IF(AGGR(COUNT(DISTINCT Sector),Customer)>1,1,0))>3,1,0)

and then using it inside sum function.

• ###### Re: Create expression as per excel in qliksense

Hi,

I can't attach the qvf in this post but it's very simple to recreate:

2. The instructions on how to create the pivot table with all formulas are in my first post,
3. The KPI that shows 2 as the result is =SUM(IF(AGGR(COUNT(DISTINCT Sector),Customer)>1,1,0))

I hope this has helped you,

Cheers,

Luis

PS. When you can please mark the appropriate replies as Correct. This will help community members know which discussions have already been addressed and have a possible known solution. Please mark replies as Helpful if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads as Helpful if you feel additional info is useful to others

• ###### Re: Create expression as per excel in qliksense

Yes I did...

Thanks Luis

• ###### Re: Create expression as per excel in qliksense

How did you create this just count and count> 0 column in pivot.

• ###### Re: Create expression as per excel in qliksense

Hi,