Qlik Community

Ask a Question

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Welcome to our newly redesigned Qlik Community! Read our blog to learn about all the new updates: READ BLOG and REPORTED ISSUES
cancel
Showing results for 
Search instead for 
Did you mean: 
Partner
Partner

Cost comparison to LY

I have an existing Sales app and I would like to compare the cost prices of the items I sold this year to the same items sold the previous year.

Currently I have this expressions to calculate this ytd values:

SUM({<[Sales_Fin_Year]=,Sales_Month=,Date = {">=$(=YearStart(Today(),0))<=$(=AddYears(Today(),0))"} >} [Unit Cost])

and SUM({<[Sales_Fin_Year]=,Sales_Month=,Date = {">=$(=YearStart(Today(),-1))<=$(=AddYears(Today(),-1))"} >} [Unit Cost]) to calculate previous ytd.

I would like to only show the costs for item sold this years as well as last year and exclude items that were sold in only one of the two years.

I am not entirely sure if this can be done but would appreciate any help in this regard.

Thanking you.

1 Solution

Accepted Solutions
Specialist
Specialist

Hi, John, try to multiply by the condition of the fact of sales in a related year

your first expr

SUM({<[Sales_Fin_Year]=,Sales_Month=,Date = {">=$(=YearStart(Today(),0))<=$(=AddYears(Today(),0))"} >} [Unit Cost])  * If( Aggr(SUM({<[Sales_Fin_Year]=,Sales_Month=,Date = {">=$(=YearStart(Today(),-1))<=$(=AddYears(Today(),-1))"} >} [Unit Cost]) , [You Item Field]) > 0, 1)

second expr

SUM({<[Sales_Fin_Year]=,Sales_Month=,Date = {">=$(=YearStart(Today(),-1))<=$(=AddYears(Today(),-1))"} >} [Unit Cost]) * If( Aggr(SUM({<[Sales_Fin_Year]=,Sales_Month=,Date = {">=$(=YearStart(Today(),0))<=$(=AddYears(Today(),0))"} >} [Unit Cost]), [You Item Field]) > 0, 1)

View solution in original post

2 Replies
Specialist
Specialist

Hi, John, try to multiply by the condition of the fact of sales in a related year

your first expr

SUM({<[Sales_Fin_Year]=,Sales_Month=,Date = {">=$(=YearStart(Today(),0))<=$(=AddYears(Today(),0))"} >} [Unit Cost])  * If( Aggr(SUM({<[Sales_Fin_Year]=,Sales_Month=,Date = {">=$(=YearStart(Today(),-1))<=$(=AddYears(Today(),-1))"} >} [Unit Cost]) , [You Item Field]) > 0, 1)

second expr

SUM({<[Sales_Fin_Year]=,Sales_Month=,Date = {">=$(=YearStart(Today(),-1))<=$(=AddYears(Today(),-1))"} >} [Unit Cost]) * If( Aggr(SUM({<[Sales_Fin_Year]=,Sales_Month=,Date = {">=$(=YearStart(Today(),0))<=$(=AddYears(Today(),0))"} >} [Unit Cost]), [You Item Field]) > 0, 1)

View solution in original post

Partner
Partner

Hi, Andrey. thank you very much for this solution. It is exactly what I was looking for.