2 Replies Latest reply: Mar 9, 2013 3:18 AM by Anil Uckunkaya RSS

    Using If functions in dimension

    Tae Won

      Hi I am a pretty new to developing qlikview but have some experience in Visual Basics and SQL.

      I am in the process of developing a dashboard that reflects my company's workflow system.
      Now in the data there are [Item Number[, [Work status], and [Hold Expiration date]. A workitem could be worked on unless the [Work Status] is on "HOLD" or if the workitem is on "HOLD" status but the hold expiration date has passed now. (which is assigned the moment workitem is put on hold).

       

      So on my chart (it is a pie chart which is supposed to represent the percentage of workitems that could be worked on) I have my dimension as:
      =if(([Work Status] <> 'HOLD') OR ([Hold Expiration Date]<now()) , 'Available for Work', 'On Hold').

      And my expressions as:

      =count(distinct[Item Number]

       

      The Pie chart displays the count correctly (and I can add another expression for percentage which i am not too concerned at this point) and I was happy to get that far. But my problem is when I select the "Available for Work" section of the pie chart, It only selects the workitems with the "Hold Expiration Date". (Say Count of "Available for Work" was 4000 before I pressed the pie chart. After the number decreases to 1900 since it is only selecting the ones with "Hold Expiration date". Since only the "HOLD" items have hold expiration date, the listbox i added below with workdetail only displays the "HOLD" Items when it should display every type of [Work Status]. Wierd thing is when i press the "On Hold" section of pie chart, the count remains the same for "On Hold" (3000 count before pressing, 3000 even after being pressed)

       

      So I am guessing it is my If function in my dimension automatically selects all the hold expiration dates? Can this problem be avoided by using set analysis?
      Thank you for looking and let me know if theres is something unclear in my question. (I would be so happy if somone can answer this)