How to use count distinct from one period to another?

[#1]

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?

[kaushiknsolanki]

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

[#3]

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

[kaushiknsolanki]

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

[#5]

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.

[Kushal_Chawda]

try this

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

[#7]

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

[reddy-s]

Hi Lasse,

Try this:

• COUNT({$<Item_Number = e({1<Year ={"$(=addyears(Year,-1))"}>}Item_Number )>} Distinct Item_Number)

[Kushal_Chawda]

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 )

[#10]

Returns the quanity of Unique Items per Year