Group By in Chart

[Manasareddy09]

Hello Qlik Enthusiasts,

I am trying to count values based on the dimension in Chart level using a variable. 

Trying to group 'Value' by COL .

Count({$<Value> COL})

i am getting count 1 for every value in COL . 

i want to get all the values assigned to that particular Value of COL .

Please help me solve this,

I hope you have a great day,

Thank you.

[bgl-sjoyce · Accepted solution]

Besides some of your field names changing and values changing, it still works so not sure what you are missing on your end.  I updated the expression to PLANNED and "Done*". 

This is the last column that shows the number of exams planned in the next x days for students that have a status of like done* in the prior x days. (x days = vInput * 30)

Count({
<Student_Id = P({<[Exam Status] = {"Done*"}, [Actual Exam Date]={">=$(=date(today() - $(vMonths)))"}>} Student_Id)
,[Exam Status] = {'PLANNED'}, [Schedule Exam Date]={"<=$(=date(today() + $(vMonths))) >= today()"}>
}
Exam_ID)

[Manasareddy09]

Hello Dear,

Thank you so much for the formula, It works now 😄

Although i did not understand the formula very well, What does the 'P' indicate in this formula ? 

Thank you for your time.

Best regards

[stevejoyce]

P is possible.  You can also use E for exclude.  Within that you can use set analysis.

This way I can use specific set analysis/filters for what Student_Id's limit to.  And have set anslysis for the entire calculation.

[Manasareddy09]

Thank you so much, I have a small Question, When i put this in the chart, It only shows the Students with duplicate exams, I also want to see the previous exam and their dates, For which i have just created another table with out the formula in , and just selecting one Student ID from the table with the formula in (So that it shows the Duplicate) and also shows all the formula's in the down table. 

But i am looking for something more Dynamic, Which shows the entries of only those students in the Second table as well. My Question is can we connect two tables in Qlik ? Or can i set a layout which says user what to do ? 

For example, Please select the months, 

You will see all the Duplicates, in the below table, and then select which ever Student you want to see and then you would see that particular student info  in the Bottom most table. 

Another Possible solution which i could think of is showing all the exams but highlighting the duplicates . 

I am not really sure. I would be happy if any of you have more ideas to make it simpler. 

Thank you so much for the great response though 🙂

[Manasareddy09]

Thank you So much, Could you check the recent comment of mine? I was thinking i was replying you but i did not, I just commented on the post, But i though i would do it just that you get a notification :=)

[stevejoyce]

I'm not sure I totally follow, but I can try.

Yes this calculation is counting the # of planned exams for each student_id for students that have taken an exam, so it is not going to show all exams as-is.  

Can you try using total in your expression by student_id and see if this is what you are thinking.  If you add exam level granularity to your table, this will still give you the student level aggregation that repeats what's displayed for each exam. 

Count(TOTAL <Student_Id> {
<Student_Id = P({<[Exam Status] = {"Done*"}, [Actual Exam Date]={">=$(=date(today() - $(vMonths)))"}>} Student_Id)
,[Exam Status] = {'PLANNED'}, [Schedule Exam Date]={"<=$(=date(today() + $(vMonths))) >= today()"}>
}
Exam_ID)

-Or yes, highlighting duplicates could be an option. 

-It could make sense to have a table or listbox that's just for duplicate students and a table that's all details.  If you want as a listbox, wrap the expression like: aggr( if (calculation>0, Student_Id), Student_Id).

-Or you can use your flag you created manually in the table and say if (calculation > 0, 'DUP', 'NAV'), that will show all records.

[Manasareddy09]

hello , 

I have tried to give the total student id in the expression but it doesnt show what i want, It is showing me all the students regardless of duplicate or  NAV, and just displaying the Student_id in the place of duplicate. 

I want to see for example,

Frist table with the formula in , shows only duplicate Exam_ID,

In second table, I want to see all the exams_ID for the duplicate student_id's 

I don't know if i am being clear here.. But please let me know i will try to explain again 🙂

I have also tried Aggr() which you mentioned but that did not work as well. 

Thank you so much.

[stevejoyce]

"Frist table with the formula in , shows only duplicate Exam_ID":

We solved this already right?

"all the exams_ID for the duplicate student_id's":

How's this?  This intersects students that have a done exam in last x days AND planned exam in next x days.  It is only limiting to student_ids, so it should show all the exams for duplicate student ids.

Count({
<Student_Id = P({<[Exam Status] = {"Done*"}, [Actual Exam Date]={">=$(=date(today() - $(vMonths)))"}>} Student_Id)>
*
<Student_Id = P({<[Exam Status] = {'PLANNED'}, [Schedule Exam Date]={"<=$(=date(today() + $(vMonths))) >= today()"}>} Student_Id)>
}
Exam_ID)

[Manasareddy09]

Student_ID, Exam_ID, Actual Date, Schedule Date,Status, Expected Output

1, 203, 12-07-2021, 12-07-2021,Done, NAV

1,202, 12-09-2021, -,Planned, DUP 

1,200, 16-05-2015, 16-05-2015,Done, NAV

2,101,11-05-2021,11-05-2021,Cancelled,NAV

5,102,03-07-2021,03-07-2021, Done, NAV

5,109, 03-04-2022,-,Planned,DUP

3,207, 12-12-2000,12-12-2000,Done,NAV

For the above data if i apply this formula, 

Count( {
<Student_Id = P({<[Exam Status] = {"Done*"}, [Actual Exam Date]={">=$(=date(today() - $(vMonths)))"}>} Student_Id)
,[Exam Status] = {'PLANNED'}, [Schedule Exam Date]={"<=$(=date(today() + $(vMonths))) >= today()"}>
}
Exam_ID)

i am getting the correct result as what i wanted which is, Below is an example of what i am getting in a table chart:

Student_ID, Exam_ID, Actual Date, Schedule Date,Status, Output

1,202, 12-09-2021, -,Planned, DUP 

5,109, 03-04-2022,-,Planned,DUP

Now what i want to to see all his/her total entries like following in another chart

Student_ID, Exam_ID, Actual Date, Schedule Date,Status, Expected Output

1, 203, 12-07-2021, 12-07-2021,Done, NAV

1,202, 12-09-2021, -,Planned, DUP 

1,200, 16-05-2015, 16-05-2015,Done, NAV

5,102,03-07-2021,03-07-2021, Done, NAV

5,109, 03-04-2022,-,Planned,DUP

Do you have any idea of how i could do it ? Is it only possible by selecting or is there a way to connect two charts and say that i only want to see the entries of the above chart values . 

I hope i am clear this time. 

I have tried the formula which you gave doesn't show the above output. 

Thank you. 

[stevejoyce]

the way you'd "connect" them is to mimic the set analysis condition.  This calculation limits to how i understand it so not sure what i'm missing.  This flags/counts that will show all exams for students that have a calculated duplicate.

Are you not able to calculate the Expected Output part?  i didn't use that or try to calculate that, this was just a flag to limit to the appropriate records.

Count({
<Student_Id = P({<[Exam Status] = {"Done*"}, [Actual Exam Date]={">=$(=date(today() - $(vMonths)))"}>} Student_Id)>
*
<Student_Id = P({<[Exam Status] = {'PLANNED'}, [Schedule Exam Date]={"<=$(=date(today() + $(vMonths))) >= today()"}>} Student_Id)>
}
Exam_ID)