Creating a string from two field values in aggr fu... - Qlik Community

Report Inappropriate Content · ‎2012-01-22

Hi all,

I have following data:

CAL.CurrMonth	CAL.CurrYear	CAL.DateWeekStart	CAL.DateWeekEnd	CAL.FiscalWMMonth	CAL.FiscalWMYear	CAL.WMMonth	CAL.WMWeek
1	2012	1/21/2012	1/27/2012	1	2011	2012/01	201152
1	2012	1/14/2012	1/20/2012	1	2011	2012/01	201151
1	2012	1/7/2012	1/13/2012	1	2011	2012/01	201150
12	2011	12/31/2011	1/6/2012	1	2011	2012/01	201149
12	2011	12/24/2011	12/30/2011	12	2011	2011/12	201148
12	2011	12/17/2011	12/23/2011	12	2011	2011/12	201147
12	2011	12/10/2011	12/16/2011	12	2011	2011/12	201146
12	2011	12/3/2011	12/9/2011	12	2011	2011/12	201145
11	2011	11/26/2011	12/2/2011	12	2011	2011/12	201144
11	2011	11/19/2011	11/25/2011	11	2011	2011/11	201143
11	2011	11/12/2011	11/18/2011	11	2011	2011/11	201142
11	2011	11/5/2011	11/11/2011	11	2011	2011/11	201141
10	2011	10/29/2011	11/4/2011	11	2011	2011/11	201140
10	2011	10/22/2011	10/28/2011	10	2011	2011/10	201139
10	2011	10/15/2011	10/21/2011	10	2011	2011/10	201138
10	2011	10/8/2011	10/14/2011	10	2011	2011/10	201137
10	2011	10/1/2011	10/7/2011	10	2011	2011/10	201136
9	2011	9/24/2011	9/30/2011	9	2011	2011/09	201135
9	2011	9/17/2011	9/23/2011	9	2011	2011/09	201134
9	2011	9/10/2011	9/16/2011	9	2011	2011/09	201133
9	2011	9/3/2011	9/9/2011	9	2011	2011/09	201132
8	2011	8/27/2011	9/2/2011	9	2011	2011/09	201131
8	2011	8/20/2011	8/26/2011	8	2011	2011/08	201130
8	2011	8/13/2011	8/19/2011	8	2011	2011/08	201129
8	2011	8/6/2011	8/12/2011	8	2011	2011/08	201128
7	2011	7/30/2011	8/5/2011	8	2011	2011/08	201127
7	2011	7/23/2011	7/29/2011	7	2011	2011/07	201126
7	2011	7/16/2011	7/22/2011	7	2011	2011/07	201125
7	2011	7/9/2011	7/15/2011	7	2011	2011/07	201124
7	2011	7/2/2011	7/8/2011	7	2011	2011/07	201123
6	2011	6/25/2011	7/1/2011	6	2011	2011/06	201122
6	2011	6/18/2011	6/24/2011	6	2011	2011/06	201121
6	2011	6/11/2011	6/17/2011	6	2011	2011/06	201120
6	2011	6/4/2011	6/10/2011	6	2011	2011/06	201119
5	2011	5/28/2011	6/3/2011	6	2011	2011/06	201118
5	2011	5/21/2011	5/27/2011	5	2011	2011/05	201117
5	2011	5/14/2011	5/20/2011	5	2011	2011/05	201116
5	2011	5/7/2011	5/13/2011	5	2011	2011/05	201115
4	2011	4/30/2011	5/6/2011	5	2011	2011/05	201114
4	2011	4/23/2011	4/29/2011	4	2011	2011/04	201113
4	2011	4/16/2011	4/22/2011	4	2011	2011/04	201112
4	2011	4/9/2011	4/15/2011	4	2011	2011/04	201111
4	2011	4/2/2011	4/8/2011	4	2011	2011/04	201110
3	2011	3/26/2011	4/1/2011	3	2011	2011/03	201109
3	2011	3/19/2011	3/25/2011	3	2011	2011/03	201108
3	2011	3/12/2011	3/18/2011	3	2011	2011/03	201107
3	2011	3/5/2011	3/11/2011	3	2011	2011/03	201106
2	2011	2/26/2011	3/4/2011	3	2011	2011/03	201105
2	2011	2/19/2011	2/25/2011	2	2011	2011/02	201104
2	2011	2/12/2011	2/18/2011	2	2011	2011/02	201103
2	2011	2/5/2011	2/11/2011	2	2011	2011/02	201102
1	2011	1/29/2011	2/4/2011	2	2011	2011/02	201101
1	2011	1/22/2011	1/28/2011	1	2010	2011/01	201052
1	2011	1/15/2011	1/21/2011	1	2010	2011/01	201051
1	2011	1/8/2011	1/14/2011	1	2010	2011/01	201050
1	2011	1/1/2011	1/7/2011	1	2010	2011/01	201049
12	2010	12/25/2010	12/31/2010	12	2010	2010/12	201048
12	2010	12/18/2010	12/24/2010	12	2010	2010/12	201047
12	2010	12/11/2010	12/17/2010	12	2010	2010/12	201046
12	2010	12/4/2010	12/10/2010	12	2010	2010/12	201045
11	2010	11/27/2010	12/3/2010	12	2010	2010/12	201044
11	2010	11/20/2010	11/26/2010	11	2010	2010/11	201043
11	2010	11/13/2010	11/19/2010	11	2010	2010/11	201042
11	2010	11/6/2010	11/12/2010	11	2010	2010/11	201041
10	2010	10/30/2010	11/5/2010	11	2010	2010/11	201040
10	2010	10/23/2010	10/29/2010	10	2010	2010/10	201039
10	2010	10/16/2010	10/22/2010	10	2010	2010/10	201038
10	2010	10/9/2010	10/15/2010	10	2010	2010/10	201037
10	2010	10/2/2010	10/8/2010	10	2010	2010/10	201036
9	2010	9/25/2010	10/1/2010	9	2010	2010/09	201035
9	2010	9/18/2010	9/24/2010	9	2010	2010/09	201034
9	2010	9/11/2010	9/17/2010	9	2010	2010/09	201033
9	2010	9/4/2010	9/10/2010	9	2010	2010/09	201032
8	2010	8/28/2010	9/3/2010	9	2010	2010/09	201031
8	2010	8/21/2010	8/27/2010	8	2010	2010/08	201030
8	2010	8/14/2010	8/20/2010	8	2010	2010/08	201029
8	2010	8/7/2010	8/13/2010	8	2010	2010/08	201028
7	2010	7/31/2010	8/6/2010	8	2010	2010/08	201027
7	2010	7/24/2010	7/30/2010	7	2010	2010/07	201026
7	2010	7/17/2010	7/23/2010	7	2010	2010/07	201025
7	2010	7/10/2010	7/16/2010	7	2010	2010/07	201024

I would like to get the last CAL.Week for each month. I used =AGGR(Date(MAX(CAL.Week)),CAL.CurrMonth). This gives me the last weeknumber for each month as expected when I only have one year to worry about. If my calendar contains data from 2009 to 2012 then my result only contains weeks for 2012. I know the limitation is based on the fact that the CAL.CurrMonth is from 1-12. Is there a way to create a sting by combining Cal.CurrMonth and Cal.CurrYear. This way I can get every last CAL.Week for each month for each year.

So basically I would like to somehow modify my AGGR function to something like =AGGR(Date(MAX(CAL.Week)),CAL.CurrMonth & CAL.CurrYear). Is this possible or do I have to load month/year as a field.

Thanks,

T

Anonymous · ‎2012-01-23

Patel,

You can create the combined field, and you can create the new field month-Year, but it is not needed fro your purpose. It is enough to aggregate by two fields:
=AGGR(Date(MAX(CAL.Week)),CAL.CurrMonth,CAL.CurrYear)

Report Inappropriate Content · ‎2012-01-23

A followup question is:

Thank you for the response.

How can I sum up the values for a field for all records containing CAL.Week using firstsortedvalue() for the above suggested aggregation?

I know we can use -CAL.week to get the last entry for each aggregation but I'm having some difficulties creating a set analsysi based on this aggregation.

I was hoping to find something along the lines of

=sum({$<CAL.week = {$(=firestsoretedvalue(CAL.week, -CAL.week)}>} Sales)

Any help would be appreciated.

Thanks,

T

Anonymous · ‎2012-02-08

The quotation marks are missing in your expression. Try this:

=sum({$<CAL.week = {"$(=firestsoretedvalue(CAL.week, -CAL.week)"}>} Sales)

Besides, it is simpler with max() function in your case:

=sum({$<CAL.week = {"$(=max(CAL.week"}>} Sales)

Creating a string from two field values in aggr function