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: 
Anonymous
Not applicable

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
Anonymous
Not applicable
Author

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)

View solution in original post

7 Replies
isingh30
Specialist
Specialist

Please share your data or app.

Thank you!

rubenmarin

Hi Steph, maybe with:

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

,

(Count(Distinct(Date)))-1

,

(Count(Distinct(Date))))

Anonymous
Not applicable
Author

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!

Budget by Day - QS.PNGBudget by Day - Current Week.PNG

Digvijay_Singh

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

rubenmarin

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

LOAD date

     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.

Anonymous
Not applicable
Author

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)

sasiparupudi1
Master III
Master III

Please close the thread by marking any helpful and a correct answer