Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
george_1955
New Contributor

Numbers of Days in selection

How calculate the number of calendar days in the period selections, regardless of the selections on the tables?

Thank You,

1 Solution

Accepted Solutions

Re: Numbers of Days in selection

May be this:

Max({1<Month = p(Month), Year = p(Year), Date = P(Date), Quarter = P(Quarter), MonthYear = p(MonthYear)>}Date) - Min({1<Month = p(Month), Year = p(Year), Date = P(Date), Quarter = P(Quarter), MonthYear = p(MonthYear)>}Date)

11 Replies

Re: Numbers of Days in selection

Can you elaborate on your requirement with an example?

rupamjyotidas
Valued Contributor

Re: Numbers of Days in selection

Maybe This for Calendar Days


Num(Max(Datefield)-Min(Datefield))

galax_allu
Valued Contributor

Re: Numbers of Days in selection

Interval (Timestamp#(TimeEnd,'DD/MM/YYYY hh:mm:ss') - Timestamp#(TimeEnd,'DD/MM/YYYY

hh:mm:ss'), 'hh:mm:ss')

MVP
MVP

Re: Numbers of Days in selection

I'm guessing you mean the total number of calendar days in the data model, ignoring all selections, and calculated in script since this is in the scripting section?

[Days]:
LOAD max([Date]) - min([Date]) as [Days In Data Model]
;
LOAD num(fieldvalue('Date',recno())) as [Date]
AUTOGENERATE fieldvaluecount('Date')
;

But I probably wouldn't bother with script. I'd use the below expression, possibly as a result stored in a variable if I was using it in several places.

max({1} Date) - min({1} Date)

george_1955
New Contributor

Re: Numbers of Days in selection

Thank you all for the reply.

Perhaps my question was not clear, and rephrase again:

I want to calculate the number of calendar days (in UI) corresponding selections of time (ex. Jan-april and year=2016),regardless of other selections they make (eg. Product, channel)?

Re: Numbers of Days in selection

May be this:

Max({1<Month = p(Month), Year = p(Year), Date = P(Date), Quarter = P(Quarter), MonthYear = p(MonthYear)>}Date) - Min({1<Month = p(Month), Year = p(Year), Date = P(Date), Quarter = P(Quarter), MonthYear = p(MonthYear)>}Date)

Re: Numbers of Days in selection

or this:

Max({1<Month = $::Month, Year = $::Year, Date = $::Date, Quarter = $::Quarter, MonthYear = $::MonthYear>}Date) - Min({1<Month = $::Month, Year = $::Year, Date = $::Date, Quarter = $::Quarter, MonthYear = $::MonthYear>}Date)

MVP
MVP

Re: Numbers of Days in selection

Do we need the p() for anything other than [Date]? All the other fields should have a direct effect on what values of [Date] are possible, and so I think selections in them would indirectly count. I may be missing something but it seems to be working on a simple example I'm fiddling with. So might this do the trick?


max({1<[Date]=p([Date])>} [Date]) -

min({1<[Date]=p([Date])>} [Date])

MVP
MVP

Re: Numbers of Days in selection

Wait, since other selections affect what is possible, neither approach works. I think we have to explicitly tell it which fields to ignore, not which ones to use. So... dollar sign expansion with a funky concat(). Hmmm... something like this, assuming all these fields are on a calendar connected only by Date to the rest of the model?

max({<[$(=concat({<[$Table]-={'Calendar'},[$Field]-={'Date'}>} distinct [$Field],']=,['))]=}>} [Date]) - min({<[$(=concat({<[$Table]-={'Calendar'},[$Field]-={'Date'}>} distinct [$Field],']=,['))]=}>} [Date])

I need to find an app with a calendar and see...

Edit: Yeah, that seems to work.

Community Browser