Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
lanlizgu
Creator III
Creator III

Date table showing last day with first day data

Hi,

I would like to have a table with the date as dimension showing the last date of each year (for the current year it should be the last day of the year).

The thing is that for the expression I would like to have the sum(values) of the first day of the year. 

I am not being able to display as dimension the last day of the year with the sum(values) of the first day of the year.

Any suggestion?

Labels (4)
1 Solution

Accepted Solutions
lanlizgu
Creator III
Creator III
Author

Hi Aiolos,

I was able to develop this last week without adding a calculated dimension.

Based on your document, the expression would be something like this:

sum({
< Date={$(=concat(distinct chr(39) & Aggr(Date(Max(Date), 'DD/MM/YYYY'),Year,Name) & chr(39), ',' ))}>
+
< Date={$(=concat(distinct chr(39) & Aggr(Date(Max(Date), 'DD/MM/YYYY'),Year,Name) & chr(39), ',' ))}>
}aggr(
sum(
{
< Date={$(=concat(distinct chr(39) & Aggr(Date(Min(Date), 'DD/MM/YYYY'),Year,Name) & chr(39), ',' ))}
>
} Value )
,Year,Name))
+
sum({
< Date={"$(=Date(Min(Date), 'DD/MM/YYYY'))"}>
}aggr(
sum(
{
< Date={$(=concat(distinct chr(39) & Aggr(Date(Min(Date), 'DD/MM/YYYY'),Year,Name) & chr(39), ',' ))}>
} Value )
,Year,Name))

 

In my example it is different as the date field is already a date and I add also in the set analysis a conditional based on other date field.

 

Thank you.

 

Best regards.

View solution in original post

18 Replies
uacg0009
Partner - Specialist
Partner - Specialist

Hi Lanlizgu,

Because I don't know your data model and what columns you have, so I created a sample for you, hope that's what you want.

Date table showing last day with first day data.PNG

Aiolos

lanlizgu
Creator III
Creator III
Author

Thank you!

I don't have access to Qlikview, could you please upload in qvf or put the set analysis?

jyothish8807
Master II
Master II

Hi Lan,

Try this:

A:
LOAD * INLINE [
Date, Sale
31/12/2017, 10
1/1/2018, 20
31/12/2018, 30
1/1/2019, 40
];

B:
Load
date(Date2-1) as Date,
Sale2;
Load
Date as Date2,
Sale as Sale2
resident A
where match(Date,Yearstart(Date));

Best Regards,
KC
jyothish8807
Master II
Master II

You can create a straight chart, with

Dim: Date

exp: sum(Sale2)

Best Regards,
KC
uacg0009
Partner - Specialist
Partner - Specialist

My test data is :
LOAD * INLINE [
Name, Start Date, Year, Value
TEST, 2018-01-01, 2018, 111
TEST, 2018-12-31, 2018, 222
TEST, 2017-01-01, 2017, 333
TEST, 2017-06-08, 2017, 222
];

Straight dimension:
Name
Year
Expression:
Last Date: =Date(Aggr(NODISTINCT Max(YearEnd([Start Date])),Name,Year),'YYYY-MM-DD')
Values: Sum(if([Start Date] = Date(Aggr(NODISTINCT Min(YearStart([Start Date])),Name,Year),'YYYY-MM-DD'),Value))

Aiolos
lanlizgu
Creator III
Creator III
Author

Thanks, but I want this to be done in set analysis, not script.

lanlizgu
Creator III
Creator III
Author

Thanks, but I am trying to develop this without ifs.

Anonymous
Not applicable


Thanks for your post! I think there are many other people who are interested in them just like me! How long does it take to complete this article? I have read through other blogs, but they are cumbersome and confusing. I hope you continue to have such quality articles to share!

Kalender Januar 2019 Mit Feiertagen

Calendario Enero 2019

uacg0009
Partner - Specialist
Partner - Specialist

Hi,

Like I said, I don't know what columns you have, so I add month and day in my table, because I think if you have month and day, the problem will be easier.

LOAD * INLINE [
Name, Start Date, Year, Value, Month, Day
TEST, 2018-01-01, 2018, 111, 1, 1
TEST, 2018-12-31, 2018, 222, 12, 31
TEST, 2017-01-01, 2017, 333, 1, 1
TEST, 2017-06-08, 2017, 222, 6, 8
];

Expression:

Values: Sum({<Month = {"1"},Day = {"1"}>}Value)