Skip to main content

Qlik

cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Announcements
Happy New Year! Cheers to another year of collaboration, connections and success.
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
geogou1973
Creator
Creator
‎2018-03-26 10:01 AM

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

802 Views
0 Likes
10 Replies
niclaz79
Partner - Creator III
Partner - Creator III
‎2018-03-26 10:06 AM

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.

611 Views
0 Likes
YoussefBelloum
Champion
Champion
‎2018-03-26 10:11 AM

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 ?

611 Views
0 Likes
geogou1973
Creator
Creator
‎2018-03-26 10:18 AM
Author

In response to niclaz79

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

611 Views
0 Likes
geogou1973
Creator
Creator
‎2018-03-26 10:20 AM
Author

In response to YoussefBelloum

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.

611 Views
0 Likes
YoussefBelloum
Champion
Champion
‎2018-03-26 10:23 AM

In response to geogou1973

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

611 Views
0 Likes
niclaz79
Partner - Creator III
Partner - Creator III
‎2018-03-26 10:24 AM

In response to geogou1973

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.

611 Views
0 Likes
olivierrobin
Specialist III
Specialist III
‎2018-03-26 10:38 AM

In response to geogou1973

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

611 Views
0 Likes
geogou1973
Creator
Creator
‎2018-03-27 02:56 AM
Author

In response to YoussefBelloum

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

611 Views
0 Likes
geogou1973
Creator
Creator
‎2018-03-27 02:58 AM
Author

In response to niclaz79

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

611 Views
0 Likes