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: 
george_1955
Contributor II
Contributor II

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
sunny_talwar

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)

View solution in original post

11 Replies
sunny_talwar

Can you elaborate on your requirement with an example?

rupamjyotidas
Specialist
Specialist

Maybe This for Calendar Days


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

Anonymous
Not applicable

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

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

johnw
Champion III
Champion III

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
Contributor II
Contributor II
Author

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)?

sunny_talwar

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)

sunny_talwar

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)

johnw
Champion III
Champion III

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])

johnw
Champion III
Champion III

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.