Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Highlighted
Contributor II

Issue with an if

I have some pretty big nested Ifs' with which I assign the amount of working days to each month that's something like this:

if(assign_date_month = 'Jan', 16

,

if(assign_date_month = 'Feb',20

,

if(assign_date_month = 'Mar',23

,

if(assign_date_month = 'Apr',18

,

if(assign_date_month = 'May',22

,

if(assign_date_month = 'June',22

,

if(assign_date_month = 'July',21

,

if(assign_date_month = 'Aug',22

,

if(assign_date_month = 'Sept' and assign_date <= '11-09-17',6

,

if(assign_date_month = 'Sept' and assign_date >= '11-09-17',14

,

if(assign_date_month = 'Oct', 22

,

if(assign_date_month = 'Nov', 21

,

if(assign_date_month = 'Dec', 18

,if(assign_date_month = 'Jan' and Year(assign_date) = 2018,5)

)))))))))))))

The exception on September is due to the fact that September is split between two work cycles(Cycle 2 is until 11-09-17 and Cycle 3 is after 11-09-17), that I can manipulate via multibox. My problem is, apparently it doesn't work, because regardless if I select Cycle 2 or Cycle 3, the September value for working days is considered null.
TL;DR: I want my value for working days in September to change depending on what cycle I select(Cycle 2 and 3 in this case). Cycle 2 is until the date of 11-09-2017 and Cycle 3 is after the date of 11-09-2017. How can I do that, preferably in the nested Ifs?

9 Replies
Highlighted
Honored Contributor

Re: Issue with an if

Can you share some sample data?

What's the format for  assign_date? Try using Date function once

Highlighted

Re: Issue with an if

If assign_date = 11-09-17, you try to stuff two distinct values (6 and 14) in whatever field or table cell you are applying this expression to. That won't work. I think (according to your explanation) that the second if condition should omit the equal sign.

Highlighted
Contributor II

Re: Issue with an if

The format for assign_date is 'DD-MM-YY'

Highlighted

Re: Issue with an if

Also make sure that your default date format accepts 11-09-17 as a date string. Check global variable DateFormat for the correct format string.

Highlighted
Contributor II

Re: Issue with an if

So something like:

if(assign_date_month = 'Sept' and assign_date <= '11-09-17',6

,

if(assign_date_month = 'Sept' and assign_date > '11-09-17',14)
?

Highlighted
Contributor II

Re: Issue with an if

Also the Date Format is set correctly to DD-MM-YY

Highlighted

Re: Issue with an if

Now it depends on context (for which we don't get any details). If you now select september, then how many different dates will go into your expression? This expression will work just fine for september if you use it in a straight table in which assign_date is a dimension.

Highlighted
Contributor II

Re: Issue with an if

I am using it in a pivot table, with assign_date_month and area as dimensions, and the nested ifs are used in an expression to assign the working days for every month.

Highlighted
Contributor II

Re: Issue with an if

Managed to find a workaround by replacing the date comparisons with WildMatch(Concat(....