Solved: Date table showing last day with first day data - Qlik Community

lanlizgu · ‎2018-12-18

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?

lanlizgu · ‎2019-01-02

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

uacg0009 · ‎2018-12-18

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.

Aiolos

lanlizgu · ‎2018-12-18

Thank you!

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

jyothish8807 · ‎2018-12-18

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 · ‎2018-12-18

You can create a straight chart, with

Dim: Date

exp: sum(Sale2)

Best Regards,
KC

uacg0009 · ‎2018-12-18

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 · ‎2018-12-18

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

lanlizgu · ‎2018-12-18

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

Anonymous · ‎2018-12-18

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 · ‎2018-12-18

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)

Date table showing last day with first day data

Date

first day

last day

Set Analysis