Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
However, it returns "0" per year
Any ideas?
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
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.?
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
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.
try this
COUNT({$<Item_Number = p({1<Year ={"$(=max(Year)-1)"}>}Item_Number )>} Quantity)
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...
Hi Lasse,
Try this:
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 )
Returns the quanity of Unique Items per Year