How to implement COUNTIFS functionality in Qlik sense scripting

[annu181818]

Hi Everyone,

Can anyone help me in implementing the below excel formula in Qlik sense scripting :

=COUNTIFS(AF:AF,[@[ACV value]],C:C,[@Account],D:D,[@Opportunity],S:S,[@[Start Date]])

(where [@[ACV value]], [@Account], [@Opportunity], [@[Start Date]] are the cell values from fields. In excel we have a functionality that we can compare cell value to the whole column values as we go down iteratively)

Basically, this formula is finding the duplicates basis multiple conditions (comparing cell values to related fields by range and criteria method in excel formula). I didn't find anything through which I can implement the same thing in Qlik sense scripting and my new field generates the count basis the conditions given.

Kindly help, due to this one of my client is waiting for the delivery.

Thanks,

Anurag

[Dalton_Ruer]

Not sure what you mean when you say your "new field generates the count basis conditions given." 

Do you mean you input a condition in to a cell like a variable?

[annu181818]

Hi Dalton,

Please have a look at the excel snapshot below:

There is a formula used in Column AG.

I have the same dataset in Qlik sense, but need to create some additional calculated columns. As you can see the formula used in the excel is generating values(numbers/counts) in a new field as "Counter 1". The same has to be created in Qlik sense scripting.

I hope, it's clear now. Could you please suggest me a solution?

Thanks in advance..!!

[Dalton_Ruer]

Ignoring HOW Excel does CountIF ... It looks like you are trying to find out if any of your deals is duplicated (has the exact same ACV value) for the same Accounts. 

Is that the idea?

[Dalton_Ruer]

If that is what you are trying to achieve then you can accomplish the count for duplicates by doing a SELF JOIN to your table using a GROUP BY clause.  

Data:
Load * Inline [
ID, Date, Category, Sub Category, Minor Category, Value
1, 4/1/2021, Sales Org, ,, 100
2, 4/17/2021, Backlog, Backlog-sum, , 75
3, 4/19/2021, Backlog, Order book, order book sum, 30
4, 4/23/2021, Backlog, Order book, overdue order, 75
5, 4/15/2021, Backlog, Order book, undue order, 92
6, 4/19/2021, Backlog, Shipment without invoicing,, 33
7, 4/16/2021, Budget Sales, ,, 81
8, 4/23/2021, difference between budget, ,, 19
];

Join (Data)
Load
Category, Value,
Count(ID) as "Count of this value"
Resident Data
Group by Category, Value;

The above load script ends up generating the following. Notice that both lines for the same Category (think account) and Value (think ACV) that are 75 show that the count was 2 as expected.

Simply ensure that all of the fields that need to be compared are put in the Group BY clause so that the count of the Primary Key Value (ID in my case) can be.

[annu181818]

Hi Dalon,

Many thanks for your efforts, I think I got the point now. Let me try it the way you mentioned (adding an extra table and joining with the existing one). If it works, I will surely accept it as a solution, otherwise, I might come back to you again. 😊