topic Re: Numbers of Days in selection in QlikView

Numbers of Days in selection

george_1955 — Mon, 17 Oct 2016 19:33:43 GMT

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

Thank You,

Re: Numbers of Days in selection

sunny_talwar — Mon, 17 Oct 2016 20:07:28 GMT

Can you elaborate on your requirement with an example?

Re: Numbers of Days in selection

rupamjyotidas — Mon, 17 Oct 2016 20:39:21 GMT

Maybe This for Calendar Days

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

Re: Numbers of Days in selection

Anonymous — Mon, 17 Oct 2016 20:46:14 GMT

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

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

Re: Numbers of Days in selection

johnw — Mon, 17 Oct 2016 20:53:54 GMT

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)

Re: Numbers of Days in selection

george_1955 — Tue, 18 Oct 2016 04:51:53 GMT

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

sunny_talwar — Tue, 18 Oct 2016 15:48:24 GMT

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

sunny_talwar — Tue, 18 Oct 2016 15:49:43 GMT

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)

Re: Numbers of Days in selection

johnw — Tue, 18 Oct 2016 22:00:20 GMT

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

Re: Numbers of Days in selection

johnw — Tue, 18 Oct 2016 22:04:46 GMT

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.

Re: Numbers of Days in selection

sunny_talwar — Tue, 18 Oct 2016 22:42:13 GMT

John -

Why would this not work?

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)

Re: Numbers of Days in selection

johnw — Tue, 18 Oct 2016 22:54:25 GMT

Ah, yeah, that should work. Or because I'm crazy and have to make things more complicated in the name of making them simpler:

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