Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

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?

13 Replies

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

Not applicable

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.?Udklip.PNG

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

Not applicable

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)

Not applicable

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 followsUdklip.PNG

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...

reddys310
Honored Contributor II

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?

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 )

Not applicable

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

Returns the quanity of Unique Items per Year