13 Replies Latest reply: Apr 2, 2016 10:59 AM by Sangram Reddy

How to use count distinct from one period to another?

Dear Qlik Sense experts,

I am new to Qlik and have been struggling to get this expression to work properly in a table.

Columns; Order Year, Unique Items (Count(Distinct Item_Number)) and Unique Items from Year to Year

The column creating difficulties is the Unique Items from Year to Year and the expression I have been trying is in the line with;

• COUNT({\$<Item_Number = {"Before(Item_Number)"}>} Distinct Item_Number)

However, it returns "0" per year

Any ideas?

• Re: How to use count distinct from one period to another?

Hi,

If you are looking for the Item Count from year to year all you have to do is.

Keep the Dimension a Year and expression as Count(Distinct Item)

You can also use the aggregation function like below.

Sum(aggr(Count(Distinct Item),Year))

Regards,

Kaushik Solanki

• Re: How to use count distinct from one period to another?

Dear Kaushik,

Thank you for the quick answer.

Maybe I did not express my intention right, what I am looking for is the quantity of distinct item numbers from one year to another, not the amount of distinct quantity of item numbers in each year.

See attached snapshot.

Any idea how to check the unique amount of item numbers in e.g. 2014 which also exist in 2013 etc.?

• Re: How to use count distinct from one period to another?

Hi,

Correct me If I am wrong.

What I understand is you want to know which products are common across all 3 years?

Regards,

Kaushik Solanki

• Re: How to use count distinct from one period to another?

Hi again

Common from one year to another.

I.e. items common from 2013 to 2014 and 2014 to 2015.

There are no data from 2012 so for the 2013 row it will obviously be 0.

An expression to indicate common items across all years is also one I would like to include.

• Re: How to use count distinct from one period to another?

try this

COUNT({\$<Item_Number = p({1<Year ={"\$(=max(Year)-1)"}>}Item_Number )>} Quantity)

• Re: How to use count distinct from one period to another?

Dear Kush,

I translated the expression to;

COUNT({\$<Item_Number = p({1<Order_date.autoCalendar.Year ={"\$(=max(Order_date.autoCalendar.Year)-1)"}>}Item_Number )>} Quantity)

It returns - in year and 0 in the calculation column, as follows

If it is to be dynamic I would presume it would use the Before function as a reference to the column consisting the Order Year?

Or it might have something to do with the new autoCalendar function by Qlik...

• Re: How to use count distinct from one period to another?

Hi Lasse,

Try this:

• COUNT({\$<Item_Number = e({1<Year ={"\$(=addyears(Year,-1))"}>}Item_Number )>} Distinct Item_Number)
• Re: How to use count distinct from one period to another?

Returns the quanity of Unique Items per Year

• Re: How to use count distinct from one period to another?

Do you need the unique count of items which do not exist in the previous years?

• Re: How to use count distinct from one period to another?

Hi again, no, I need the unique count of what does exist in year in question AND did exist in the previous year (singular). I.e. distinct items that exist in both year and year -1

• Re: How to use count distinct from one period to another?

Hi Lasse,

If I understood your issue correctly, you need the distinct count of common items in this year and the previous year.

If so try this expression:

Expecting that you having a master calendar:create flags for this year and previous years using the inYear() function.

Once done: use this expression

count( {<items = P({1<[last year flag]={1}>} items)>}>} distinct items)

This will give you only the distinct count of common items bought in both the years

Thanks,

Sangram.

• Re: How to use count distinct from one period to another?

do you have same field name Quantity . Instead it is sum

Sum({\$<Item_Number = p({1<Order_date.autoCalendar.Year ={"\$(=max(Order_date.autoCalendar.Year)-1)"}>}Item_Number )>} Quantity)

or

Count({\$<Item_Number = p({1<Order_date.autoCalendar.Year ={"\$(=max(Order_date.autoCalendar.Year)-1)"}>}Item_Number )>} Item_Number )

• Re: How to use count distinct from one period to another?

Hi Kush,

Thank you for getting back again, it still return empty year column and 0 Unique Items Year to Year.