Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On Demand Webinar: See Why Thousands of QlikView Users Have Switched to Qlik Sense. REGISTER
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

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
Partner

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

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
Partner

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

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

lanlizgu
Creator III
Creator III

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

calendarphoto
Contributor
Contributor


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
Partner

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)