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

A distinct and chart grouping issue

Hi,

I have generated a set of data as follows.  There is other data in the set too, but not relevant for this issue.

ID    Date        Week Commencing Date     All records here for example have set and Validsetdate correct for the expression below.

2     1/9/11      29/8/11

3     30/9/11    26/9/11

4     21/9/11    19/9/11

5     19/10/11  17/10/11

6     24/10/11   24/10/11

5     8/11/11     7/11/11

7     10/11/11   7/11/11

What I want to do is show the line chart grouping (dimension) by week commencing date.  But also I want to distinct the ID field, so ID person 5 only appears once in the chart.  I'm trying to do this by this expression:

=Count(DISTINCT if(Set = 'ForThisChart' and ValidSetDate=1),ID)

What this is doing is a distinct for each of the week commencing groups in the chart.  Therefore ID 5 is still appearing twice in the report, once in W/C 17/10 and once in the W/C 7/11.

Is there anyway to handle this to remove that second 5 person here?  I assumed the distinct would do it, but it's not.  It would be a real headache to remove the second ID from the underlying dataset!

Thanks!
Nick

8 Replies
rohit214
Creator III
Creator III

hi

if i am getting you correct....

do you want to remove

5     8/11/11     7/11/11

am i right..

thanks

rohit

Not applicable
Author

Hi Rohit,

Yes, that's right.  I only want the first instance of any ID that is picked up.

Not applicable
Author

Hi Rohit,

Yes, that's right. I only want the first instance of any ID that is picked up.

rohit214
Creator III
Creator III

hey nick

remove if(Set = 'ForThisChart' and ValidSetDate=1)

because this condition creates problem..may be by using that condition lies on that condition

may it helps you

thanks

rohit

Not applicable
Author

Thanks Rohit,

Unfortunately, I can't remove that IF statement.  I need that to drop the other records in the dataset that are nothing to do with this chart (I didn't list those in the example, but there are 1000s of records this IF removes).

So I need that IF in place........

john_duffy
Partner - Creator III
Partner - Creator III

Hi Nick.

Not sure I fully understand the issue.  Are you trying to only processes IDs with the minimum Commencing Date?

If so, how about adding a flag to the data set in the load scripts to indicate the min Comm. Date for each ID.

eg.

Load

        ID, Date, Comm_Date

From Table1

     ;

left join

Load

         ID,

         'Y' as Min_Comm_Date_Flag

         min(Comm_Date) as Comm_Date

From Table1

Group by ID

     ;

Then only process records with Min_Comm_Date_Flag = 'Y' in your expression.  Note:  The above code has not been tested.

John.

Not applicable
Author

Thanks John,

That gave me a good idea for the SProc to bring back another date based on similar criteria to what you've said here!  That gets the IDs into the same date groups on the chart so the distinct expression then works perfectly!

Cheers

Nick

rohit214
Creator III
Creator III

hey....

use intervalmatch(ID,[Week Commencing Date ])

may it helps you

thanks

rohit