Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

selecting first day of the first month of the selected year

Hello,

I wonder how to select the first day of the first month of the selected year and the last day of the last month of the year selected, and when selecting a month he should skip this selection. I need to show the number of months of validity of a contract during the year, regardless of the selection of the month.

thank you!

1 Solution

Accepted Solutions
MVP
MVP

Re: selecting first day of the first month of the selected year

Ok and your date / year / month fields you select in are not connected to the spreadsheet?

Then maybe like

if (isnull (dtCancelamento) = -1 or dtCancelamento> = makedate(YEARFIELD,12,31), makedate(YEARFIELD,12,31), dtCancelamento) - if (dtCredenciamento <= makedate(YEARFIELD,1,1), makedate(YEARFIELD,1,1), dtCredenciamento)

You can also use yearend() and yearstart() functions, and check out the other date and time functions.

Hope this helps,

Stefan

4 Replies
MVP
MVP

Re: selecting first day of the first month of the selected year

>and when selecting a month he should skip this selection.

Do you mean skipping selection in month or year / date?

I assume you want to disregard the selection in month. If you need to calculate an aggregate with first day of year selected, last day of year selected and selection in month skipped, you could use set analysis, something like:

=sum({<Date = {'$(=Date(Yearstart(min(Year))))','$(=Date(daystart(Yearend(max(Year)))))'}, Month=, >} Value)

Selecting these two dates only looks a bit strange too me when asking for number of months of validity , so maybe I misunderstood something completely. Would be helpful if you could detail a bit more how you calculate the number of months.

Regards,

Stefan

Not applicable

Re: selecting first day of the first month of the selected year

I have a spreadsheet with a provider, date of accreditation and the date of cancellation by selecting the year 2012 for example, the user wants to display the following: (If the cancellation date is null or> = the last day of last month the selected year (12.31.2012), 12.31.2012, but considering the date of cancellation - (if the date of accreditation is <= the first day of first month of the selected year (01/01/2012) 01/01/2012, date of accreditation).

Ex: if (isnull (dtCancelamento) = -1 or dtCancelamento> = 31/12/2012, 31/12/2012, dtCancelamento) - if (dtCredenciamento <= 01/01/2012, 01/01/2012, dtCredenciamento). Need to know how I get on the values ​​31/01/2012 and 01/01/2012 with the selection of the year ignoring the month if it is selected.

Tks.

MVP
MVP

Re: selecting first day of the first month of the selected year

Ok and your date / year / month fields you select in are not connected to the spreadsheet?

Then maybe like

if (isnull (dtCancelamento) = -1 or dtCancelamento> = makedate(YEARFIELD,12,31), makedate(YEARFIELD,12,31), dtCancelamento) - if (dtCredenciamento <= makedate(YEARFIELD,1,1), makedate(YEARFIELD,1,1), dtCredenciamento)

You can also use yearend() and yearstart() functions, and check out the other date and time functions.

Hope this helps,

Stefan

Not applicable

Re: selecting first day of the first month of the selected year

Hello Stefan

Thanks!!!!

Juh