Skip to main content
Announcements
July 15, NEW Customer Portal: Initial launch will improve how you submit Support Cases. IMPORTANT DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
juagarti
Contributor III
Contributor III

show the last data in the month

Hello,

I'm making a chart with this data

 

datesales
07/01/20141000
17/01/20142332
17/02/20141100
27/02/20143464
20/03/20141120
30/03/20146344
15/04/20144333
30/04/20142100
01/05/20141132
11/05/20146789
09/06/20141456
19/06/20143456
11/07/20143654
21/07/20143462
03/08/20141569
13/08/20142356
08/09/20145482
10/09/20143421
22/10/20146652
29/10/20147532
01/11/20148754
26/11/20144125
16/12/20146325
26/12/20143564
11/01/20159856
21/01/20153222
14/02/20155432
24/02/20153464
13/03/20154654
23/03/20156422
22/04/20155489
30/04/20153554
08/05/20153235
28/05/20152145
05/06/20155236
25/06/20152346


in the scrypt I get year, month and week, this is ok.

Now I want to show in a bar chart (with month as the dimensión), the sales of the last week of the month. For example with the data before:

2014

Jan  2332

Feb 3464

Mar 6344

Aprl 2100

May 6789

Jun 3456

etc

How can I do it?

thanks very much!!

1 Solution

Accepted Solutions
miikkaqlick
Partner - Creator II
Partner - Creator II

Hi!

You could also do this in script. Make little table holding only necessary values. You can then use them as is or for example map those to fact-table.

Example:

I made excel-file from your example data for easy load.

Data:

LOAD date as Date,

     sales as Sales,

     Year(date)&Num(Month(date),'00') as MonthNum

FROM

(ooxml, embedded labels, table is Sheet1);

tmp1:

Load

    Max(Date) as MaxDate,

    MonthNum as M

Resident Data

Group By MonthNum;

Left Join

Load

    Num(Date) as MaxDate,

    Sales as MaxSales

Resident Data;

Now you can easily make chart like this:

Br,

Miikka

Climber Finland

View solution in original post

7 Replies
vardhancse
Specialist III
Specialist III

Last 5 Years Sales

Sum({<Year=, Quarter=, Month=, Week=, Date={‘>=$(=YearStart(Max(Date), -4))<=$(=Date(Max(Date)))’}>} Sales )

Last 6 Quarters Sales

Sum({<Year=, Quarter=, Month=, Week=, Date={‘>=$(=QuarterStart(Max(Date), -5))<=$(=Date(Max(Date)))’}>} Sales )

Last 12 Months Sales

Sum({<Year=, Quarter=, Month=, Week=, Date={‘>=$(=MonthStart(Max(Date), -11))<=$(=Date(Max(Date)))’}>} Sales )

Last 15 Weeks Sales

Sum({<Year=, Quarter=, Month=, Week=, Date={‘>=$(=WeekStart(Max(Date), -14))<=$(=Date(Max(Date)))’}>} Sales )

Last 10 Days Sales

Sum({<Year=, Quarter=, Month=, Week=, Date={‘>=$(=Date(Max(Date)-9))<=$(=Date(Max(Date)))’}>} Sales )

Note:

YTD - Year to Date

QTD - Quarter to Date

MTD - Month to Date

WTD - Week to Date    

Last YTD for the period selected

Sum({<Year=, Quarter=, Month=, Week=, Date={‘>=$(=YearStart(Max(Date), -1))<=$(=AddYears(Max(Date), -1))’}>} Sales)

Last QTD for the period selected

Sum({<Year=, Quarter=, Month=, Week=, Date={‘>=$(=QuarterStart(Max(Date), -1))<=$(=AddMonths(Max(Date), -3))’}>} Sales)

Last MTD for the period selected

Sum({<Year=, Quarter=, Month=, Week=, Date={‘>=$(=MonthStart(Max(Date), -1))<=$(=AddMonths(Max(Date), -1))’}>} Sales)

Last WTD for the period selected

Sum({<Year=, Quarter=, Month=, Week=, Date={‘>=$(=WeekStart(Max(Date), -1))<=$(=Date(Max(Date) -7))’}>} Sales)

Anonymous
Not applicable

How do you define the last week of the month ?

You expect output for Jan 2014 is 2332 which is from date 13/01/2014 which does not look like the last week.

juagarti
Contributor III
Contributor III
Author

the last week of the month is the last date that I have data, for example, the last week of january in this case:

week(17/01/2014)

07/01/20141000
17/01/20142332

because is the last date of january that i have data

How i can identify the last date of a month?? is this the data that I want to show in the bar.

with the data tha I have put in mi first post, I want to show in the bar

2014

Jan  2332

Feb 3464

Mar 6344

Aprl 2100

May 6789

Jun 3456

that are the last data of each month

juagarti
Contributor III
Contributor III
Author

thank you, but with this I can't resolve my question. I want to show in the bar char with monthname(date) as the dimension the last data of the month. I mark in bold this data:

datesales
07/01/20141000
17/01/20142332
17/02/20141100
27/02/20143464
20/03/20141120
30/03/20146344
15/04/20144333
30/04/20142100
01/05/20141132
11/05/20146789
09/06/20141456
19/06/20143456
11/07/20143654
21/07/20143462
03/08/20141569
13/08/20142356
08/09/20145482
10/09/20143421
22/10/20146652
29/10/20147532
01/11/20148754
26/11/20144125
16/12/20146325
26/12/20143564
11/01/20159856
21/01/20153222
14/02/20155432
24/02/20153464
13/03/20154654
23/03/20156422
22/04/20155489
30/04/20153554
08/05/20153235
28/05/20152145
05/06/20155236
25/06/20152346

thank you!

juagarti
Contributor III
Contributor III
Author

If I put

=SUM(if(date='$(=MaxString(date))',sales))

inly show me the data of the last wekk of the last month. I want to show the data of the last week of all month of the year

qlik.JPG

miikkaqlick
Partner - Creator II
Partner - Creator II

Hi!

You could also do this in script. Make little table holding only necessary values. You can then use them as is or for example map those to fact-table.

Example:

I made excel-file from your example data for easy load.

Data:

LOAD date as Date,

     sales as Sales,

     Year(date)&Num(Month(date),'00') as MonthNum

FROM

(ooxml, embedded labels, table is Sheet1);

tmp1:

Load

    Max(Date) as MaxDate,

    MonthNum as M

Resident Data

Group By MonthNum;

Left Join

Load

    Num(Date) as MaxDate,

    Sales as MaxSales

Resident Data;

Now you can easily make chart like this:

Br,

Miikka

Climber Finland

juagarti
Contributor III
Contributor III
Author

thank you!!!!!!