topic Re: If Statement with Set Analysis in QlikView

If Statement with Set Analysis

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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!

Re: If Statement with Set Analysis

isingh30 — Tue, 06 Mar 2018 19:09:31 GMT

Please share your data or app.

Thank you!

Re: If Statement with Set Analysis

rubenmarin — Tue, 06 Mar 2018 19:21:53 GMT

Hi Steph, maybe with:

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

(Count(Distinct(Date)))-1

(Count(Distinct(Date))))

Re: If Statement with Set Analysis

Anonymous — Tue, 06 Mar 2018 22:17:38 GMT

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!

Re: If Statement with Set Analysis

Digvijay_Singh — Tue, 06 Mar 2018 23:16:17 GMT

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

Re: If Statement with Set Analysis

rubenmarin — Wed, 07 Mar 2018 09:46:11 GMT

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.

Re: If Statement with Set Analysis

Anonymous — Wed, 07 Mar 2018 20:16:13 GMT

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)

Re: If Statement with Set Analysis

sasiparupudi1 — Fri, 09 Mar 2018 10:31:12 GMT

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