Skip to main content
Announcements
Live today at 11 AM ET. Get your questions about Qlik Connect answered, or just listen in. SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
geogou1973
Creator
Creator

compare current dates of current year with the previous one of last year

Hello,

I have a project to compare the current sale dates with the previous one of the last year.

I want the days of the month March/2018                       with the same of March/2017

                                                                   1/3/2018                            2/3/2017

                                                                   2/3/2018                            3/3/2017

                                                                   3/3/2018                            4/3/2018

                                                                   ..............                              ............

                                                                  25/3/2018                           26/3/2017

-364 days and the report i want to have the following icon

Date            Store          Amount          Amount Last Year 

1/3/2018      101            1.000                     1.500

If i create a variable i can only use one value. Also i don't want to create a calendar.

How can i do this. Can anyone help me ?

Thank you in advance

10 Replies
niclaz79
Partner - Creator III
Partner - Creator III

Hi,

You can use the function AddYear(Date,-1) to show the value one year prior and use it in a set expression;

Sum({<Date = {$(=AddYear(Date,-1))}>}Amount)

You may need to adjust the final expression based on your data model, but a version of the above should fit what you are requesting.

YoussefBelloum
Champion
Champion

Hi,

by Icons, you mean textboxe's ? can you explain a little bit the objects type you're using and how you cant to display the expected result ?

geogou1973
Creator
Creator
Author

Hello Niclas

The command AddYears(Date,-1) returns the same date one year before. I want to have 364 days before


lets say 24/3/2018 with 25/3/2017 Saturday with Saturday

geogou1973
Creator
Creator
Author

Hello Youssef,

I want a chart with the current date and the store as dimension and the amount of current date and store as first expression and the amount of the same day 364 days before in the other expression.

YoussefBelloum
Champion
Champion

if it is possible, try to attach some rows from your data

niclaz79
Partner - Creator III
Partner - Creator III

Hi,

Can't you simply add one more day after you do the Addyears(-1) function? And the 364 days are on the days that are not leap years, for those you need to go 365. Using the existing date functions in Qlikview would be more robust.

olivierrobin
Specialist III
Specialist III

hello

wgy don't you create a history date column computed with date-364

and use a set analysis in your chart

eg: sum(<date=p(history_date )>)

geogou1973
Creator
Creator
Author

Hello again,

I created variables for the previous period and i created the following command in the expression

for the current period

Sum({<SALES_YEAR={'$(=vYear)'},SALES_MONTH={'$(=vMonth)'}>} SALES)

for the previous period

sum({<SALES_DATE={'>=$(date_var_min)<=$(date_var_max)'}>}SALES)

in the dimension i have the SALES_DATE and i take the following result

   

SALES_DATEAMOUNT CURRENTAMOUNT PREVIOUS
02/03/20170,00236.607,39
03/03/20170,00290.622,46
04/03/20170,00440.174,21
06/03/20170,00245.483,87
07/03/20170,00205.953,33
08/03/20170,00199.985,84
09/03/20170,00206.829,82
10/03/20170,00290.033,07
11/03/20170,00480.385,47
13/03/20170,00253.924,48
14/03/20170,00207.943,25
15/03/20170,00231.193,85
16/03/20170,00211.188,48
17/03/20170,00266.167,47
18/03/20170,00425.862,44
20/03/20170,00219.373,72
21/03/20170,00207.900,07
22/03/20170,00233.683,70
23/03/20170,00268.157,03
24/03/20170,00406.439,58
01/03/2018231.068,680,00
02/03/2018259.546,650,00
03/03/2018412.769,270,00
05/03/2018216.490,480,00
06/03/2018178.119,500,00
07/03/2018186.208,000,00
08/03/2018184.047,690,00
09/03/2018228.499,770,00
10/03/2018354.693,330,00
12/03/2018195.834,970,00
13/03/2018183.747,840,00
14/03/2018173.932,840,00
15/03/2018191.907,440,00
16/03/2018238.288,320,00
17/03/2018353.843,080,00
19/03/2018205.591,230,00
20/03/2018185.607,200,00
21/03/2018191.999,750,00
22/03/2018208.905,980,00
23/03/2018260.027,190,00
24/03/2018350.842,020,00

if you see i have also the dates for the last period. i want to have only the dates for the current period and the amount for the current and previous period like this

   

SALES_DATEAMOUNT CURRENTAMOUNT PREVIOUS
01/03/2018231.068,68236.607,39
02/03/2018259.546,65290.622,46
03/03/2018412.769,27440.174,21
05/03/2018216.490,48245.483,87
06/03/2018178.119,50205.953,33
07/03/2018186.208,00199.985,84
08/03/2018184.047,69206.829,82
09/03/2018228.499,77290.033,07
10/03/2018354.693,33480.385,47
12/03/2018195.834,97253.924,48
13/03/2018183.747,84207.943,25
14/03/2018173.932,84231.193,85
15/03/2018191.907,44211.188,48
16/03/2018238.288,32266.167,47
17/03/2018353.843,08425.862,44
19/03/2018205.591,23219.373,72
20/03/2018185.607,20207.900,07
21/03/2018191.999,75233.683,70
22/03/2018208.905,98268.157,03
23/03/2018260.027,19406.439,58
24/03/2018350.842,020,00

how can i do this ?

Thank you in advance

geogou1973
Creator
Creator
Author

Hello again,

I created variables for the previous period and i created the following command in the expression

for the current period

Sum({<SALES_YEAR={'$(=vYear)'},SALES_MONTH={'$(=vMonth)'}>} SALES)

for the previous period

sum({<SALES_DATE={'>=$(date_var_min)<=$(date_var_max)'}>}SALES)

in the dimension i have the SALES_DATE and i take the following result

  

SALES_DATEAMOUNT CURRENTAMOUNT PREVIOUS
02/03/20170,00236.607,39
03/03/20170,00290.622,46
04/03/20170,00440.174,21
06/03/20170,00245.483,87
07/03/20170,00205.953,33
08/03/20170,00199.985,84
09/03/20170,00206.829,82
10/03/20170,00290.033,07
11/03/20170,00480.385,47
13/03/20170,00253.924,48
14/03/20170,00207.943,25
15/03/20170,00231.193,85
16/03/20170,00211.188,48
17/03/20170,00266.167,47
18/03/20170,00425.862,44
20/03/20170,00219.373,72
21/03/20170,00207.900,07
22/03/20170,00233.683,70
23/03/20170,00268.157,03
24/03/20170,00406.439,58
01/03/2018231.068,680,00
02/03/2018259.546,650,00
03/03/2018412.769,270,00
05/03/2018216.490,480,00
06/03/2018178.119,500,00
07/03/2018186.208,000,00
08/03/2018184.047,690,00
09/03/2018228.499,770,00
10/03/2018354.693,330,00
12/03/2018195.834,970,00
13/03/2018183.747,840,00
14/03/2018173.932,840,00
15/03/2018191.907,440,00
16/03/2018238.288,320,00
17/03/2018353.843,080,00
19/03/2018205.591,230,00
20/03/2018185.607,200,00
21/03/2018191.999,750,00
22/03/2018208.905,980,00
23/03/2018260.027,190,00
24/03/2018350.842,020,00

if you see i have also the dates for the last period. i want to have only the dates for the current period and the amount for the current and previous period like this

  

SALES_DATEAMOUNT CURRENTAMOUNT PREVIOUS
01/03/2018231.068,68236.607,39
02/03/2018259.546,65290.622,46
03/03/2018412.769,27440.174,21
05/03/2018216.490,48245.483,87
06/03/2018178.119,50205.953,33
07/03/2018186.208,00199.985,84
08/03/2018184.047,69206.829,82
09/03/2018228.499,77290.033,07
10/03/2018354.693,33480.385,47
12/03/2018195.834,97253.924,48
13/03/2018183.747,84207.943,25
14/03/2018173.932,84231.193,85
15/03/2018191.907,44211.188,48
16/03/2018238.288,32266.167,47
17/03/2018353.843,08425.862,44
19/03/2018205.591,23219.373,72
20/03/2018185.607,20207.900,07
21/03/2018191.999,75233.683,70
22/03/2018208.905,98268.157,03
23/03/2018260.027,19406.439,58
24/03/2018350.842,020,00

how can i do this ?

Thank you in advance