DAX conversion to Qlik Sense - duplicate row count

[Abhineo]

Hi,

I am just a week old in Qliksense, however coded similar DAX in power BI. Need help to convert it Qlik Sense.

Below DAX i wrote to get duplicate file count based on there document title.  Reviewid is autonumber (index) in the table,  doctitle is document name. If there are multiple duplicate doctitle then below DAX add one more column and keep on adding 1 in each row if duplicate doctitle found. 

Duplicate Doc # =

VAR number =

calculate(COUNTROWS('docreview reviews'),

FILTER('docreview reviews',

'docreview reviews'[doctitle] = EARLIER('docreview reviews'[doctitle])

&& 'docreview reviews'[reviewid] < EARLIER('docreview reviews'[reviewid])))

RETURN IF(ISBLANK(number),1,number +1)

Output is below which i need in Qlik Sense too: please help

Reviewid	doctitle	Duplicate Doc #
1	abcde	5
2	abcde	4
3	abcde	3
4	abcde	2
5	abcde	1
6	xyz	2
7	xyz	1

[rubenmarin · Accepted solution]

Hi, you can use: If(Below(TOTAL doctitle)=doctitle, Below(TOTAL Duplicate)+1, 1)

But selectons can change that numbers, if you want fixed numbers to count duplicates you can do it in script, something like:

Data:
LOAD 
	Reviewid,
	doctitle,
	If(doctitle=Peek(doctitle), Peek(Duplicate)+1, 1) as Duplicate
Resident
	OrigData // Table name with data
Order By
	doctitle, Reviewid desc
;
DROp Table OrigData;

This can also help with intercalated doctitles.

[Abhineo]

Thank you very much, it worked well.