Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Quy_Nguyen
Contributor III

Sales comparison vs last year

Hello,

I have the data sample and expected result like this:

1.PNG

I used:

If(Flag_This_Year = 1, Date) for the dimension,

Sum({<Date ={"$(=AddYears(Max(Date),-1))"}>}Sales) for measure sale last year, but got wrong result.

I know that it can be done with join function in data load script, I am looking for a solution on dashboard.

Any help would be greatly appreciated.

1 Solution

Accepted Solutions
rajeshthakur292
Contributor II

Re: Sales comparison vs last year

Hi,

Please see if this helps.

Thanks,

RT

14 Replies
vishalpujari22
New Contributor III

Re: Sales comparison vs last year

hi Quy You are restricting the Dimesnion field i.e If(Flag_This_Year = 1, %Date) lasy year will show "0" sales

Quy_Nguyen
Contributor III

Re: Sales comparison vs last year

Hi Vishal,

Thanks for your reply. So could you please guide me how to achieve the result table?

vishalpujari22
New Contributor III

Re: Sales comparison vs last year

Could you tell me the Expected  Result So i can change the expression!

Quy_Nguyen
Contributor III

Re: Sales comparison vs last year

Expected result is the picture in the original post

big_dreams
Contributor III

Re: Sales comparison vs last year

Hi,

try below logic

Table1:

Load * from table1;

Noconcatenate:

Current_year_Data:

Load date as year_date,

         sales as this_year_sales

Resident Table1

where flag_this_year=1;

join

Load date(AddYear(date,1)) as year_date,

         sales as prev_year_sales

Resident Table1

where flag_this_year=0;

In front end

take straight able

dimension

year_date

Expression:

1: sum(this_year_sales)

2: sum(prev_year_sales)

Regards,

Quy_Nguyen
Contributor III

Re: Sales comparison vs last year

Thanks for your reply, i am looking for a solution in front end without changing script. Is it possible?

rajeshthakur292
Contributor II

Re: Sales comparison vs last year

Hi ipcducquy,

Please see if the attached helps you.

Thanks

RT

agigliotti
Honored Contributor II

Re: Sales comparison vs last year

I guess you can't.

Quy_Nguyen
Contributor III

Re: Sales comparison vs last year

Thanks Rajesh, but i want to view data by date, not by month. I got 60 dates in 2 months of 2017 and 60 dates in the same months of 2018.

Community Browser