New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Now
cancel
Showing results for
Did you mean:
Highlighted
Contributor II

If Statement with Set Analysis

I have a formula where my denominator needs to be the number of days in the week, UNLESS it is the current week and then it needs to be number of days less 1 (so that it does not count the current day).

Seems pretty straight forward... but can't get it going.

I thought I could use the {1} in the set expression and say "If the max week number (ignoring selection) = the selected week number, count the days less 1, if not, count the days."

If(({<[WeekEndWk#]={"1(=Max(WeekEndWk#))"}>}

=

{<[WeekEndWk#]={"\$(=Max(WeekEndWk#))"}>})

,

(Count(Distinct(Date)))-1

,

(Count(Distinct(Date))))

Any suggestions on correcting syntax or an easier way?

Thanks!

1 Solution

Accepted Solutions
Highlighted
Contributor II

Re: If Statement with Set Analysis

Thank you all for your help!

I got a formula that accomplishes what I was looking for. May not be the prettiest per se... but it works!

If([WeekEndWk#]=Week(Today()),

((Num(Today()) - Num(Date(WeekStartDate)))),

((Num(Date(WeekEndDate))) - (Num(Date(WeekStartDate))))+1)

7 Replies
Highlighted
Specialist

Thank you!

Highlighted
MVP

Re: If Statement with Set Analysis

Hi Steph, maybe with:

If([WeekEndWk#]=Max({1} [WeekEndWk#])

,

(Count(Distinct(Date)))-1

,

(Count(Distinct(Date))))

Highlighted
Contributor II

Re: If Statement with Set Analysis

Ah so close!

I think this is on the right track, still giving me zeroes, but better than just null.

Attached is a screenshot with some sample info - for week 9 (not the current week) I would want the formula to pick up on 7 days worth of sales. But in the current week (even though there's been 3 days since today is Tuesday) I only want it to count the two days that have sales, whereas the current day count is 3.

Hope this helps!

Highlighted
Master III

Re: If Statement with Set Analysis

May be this, What exactly you are counting? No of days for each SiteID/Week#?

If([WeekEndWk#]=Max({1} [WeekEndWk#])

,

Aggr(Count(Distinct(Date)))-1,SiteID,[WeekEndWk#])

,

Aggr((Count(Distinct(Date)))),SiteID,[WeekEndWk#])

Highlighted
MVP

Re: If Statement with Set Analysis

Thereare many possibilities on what if failing, maybe the easiest way is to load a flag in calendar table (or where date field is):

If(Floor(date)=today(), 1, 0) as isToday

...

and use:

Count(Distinct {<isToday={0}>} Date)

Edit: forgot to add that similar flags can be added to get max week or other needs.

Highlighted
Contributor II

Re: If Statement with Set Analysis

Thank you all for your help!

I got a formula that accomplishes what I was looking for. May not be the prettiest per se... but it works!

If([WeekEndWk#]=Week(Today()),

((Num(Today()) - Num(Date(WeekStartDate)))),

((Num(Date(WeekEndDate))) - (Num(Date(WeekStartDate))))+1)

Highlighted
Master III