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]

Count(Value).

Doesn't sound like set analysis is necessary.  This is a straight count calculation:  Count(Value).  The calculation automatically groups by your dimensions so no need to explicitly try and "group by COL".

[Manasareddy09]

Hello 🙂

When i give count, it is count 1 for every ID. I have given the values below, Please check.

I have a column which has ID's, And another column with exams he/she has taken. Every Time a student takes an exam/Schedules an exam , he or she will be assigned with a unique number. 

Main task is to identify if there are any students who have already taken the test in the past one year and cleared the exam , are scheduled in the coming year.

I was trying to calculate the duplicate tests, Like student_id=1, Where the exam has been done, but the exam is schedule again within one year. I want to call it as a duplicate. 

I was able to do something close in the load script but i want to use a variable input chart so that the user can enter the values . If i am using variables in load script it is not working in this case, So i have to use front end variables itself. 

I am able to give the if else conditions but i don't know how to do group by using the Student_ID col, 

Student_ID, Exam_ID, Dates, Status, Expected Output

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

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

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

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

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

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

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

Thank you for taking out time 🙂

[stevejoyce]

With Student_ID as your dimension, the below calculation will count the number of "Planned" exams for each student in the last year, for students that have a "Done" status in the last year.

Students with a result > 0 have a planned & done within the last year.

Count({<
/*students that have a done status within last year*/
Student_ID = P({<Status = {'Done'}, Dates={">=$(=AddYears($(=max(Dates)),-1))"}>} Student_ID)
/*and exam status of Planned within last year*/
,Status ={'Planned'}
,Dates={">=$(=AddYears($(=max(Dates)),-1))"}
>}
Exam_ID)

[Manasareddy09]

Hello ,

I have tried your formula, But it throws an error "Set Modifier ad Hoc Element list ',' or ')' expected". i have cross checked. I have typed the same formula. 

also, I forgot to mention that we have another column with the schedule date. so i have entered the values again below. I have considered the Schedule date in the second part of the column. Also i am using a formula to calculate months, For example, If the user enters 3, then it subtracts 3*30 from the current date and checks the actual date and then checks if there is any exam scheduled to him in the next three months. So i am using a variable called vmonths. 

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

Note: Every time a test is schedule a new exam_id is assigned and set a status to planned, and once the exam is done the result is assigned. So if there is a duplicate, we will have two ID's assigned to that particular student in that time period. I need to calculate if there are any wrongly scheduled exams if there are any to reduce the work. 

Thanks Again 🙂

[stevejoyce]

Can you check if your date field is being loaded as a type Date.  It should if the formatting (i.e. 'DD-MM-YYYY') matches your Set DateFormat variable.  You can also manipulate the string to date using MakeDate and substring for year, month, date.

[Manasareddy09]

So far, 

I have a following variables :

/*Getting the count of students who had exam in the past x months

vActual =if(Actual Date >= Today() - $(vMonths) and Actual Date<= Today() and Wildmatch(Status,'*Done*','*Planned*' ) and Student_ID <>'NAV',Count(<Exam_id> Student_ID))

/*Getting the count of students who has an exam in the next x months

vSchedule =if(ScheduleDate <= Today() + $(vMonths) and ScheduleDate>= Today() and Wildmatch(Status,'*Planned*' ) and Student_ID <>'NAV',Count(<Exam_id> Student_ID))

/*Varaible to calculate months from user input

vMonths = $(vInput)*30 

/*Getting user input

$(vInput)is assigned to a input box. 

I don't know if this is the easiest or if i made it even complex. any suggestions would be very helpfull 🙂

Note: Whole Idea is to find the wrongly scheduled exams and reduce the work . The scheduled exams will not have a actual date. But not viceversa. 

[Manasareddy09]

It is being loaded as 'YYYY-MM-DD'. And TODAY() functions work fine with other formulas . 

[stevejoyce]

Do you have the name of your date fields switched?  All the planned exams have null schedule date, would have thought it would be the opposite.

Anyway... how i understand it, this would be the expression.

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

Also,

Convert your fields to date fields.  The - text is causing it not to evaluate correctly.  I added this preceding load to your inline.

test:
load
*
,if([Actual Date]='-', null(), Date#([Actual Date])) as newActualDate
,if([Schedule Date]='-', null(), Date#([Schedule Date])) as newScheduleDate

;
load

*

inline [

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

]

;

drop fields [Actual Date], [Schedule Date];
rename fields newActualDate to [Actual Date], newScheduleDate to [Schedule Date];

[Manasareddy09]

Yes, Sorry The dates columns there have been switched. I have tried your formula. It just shows Blanks. 

and in the timeframe today()+$(vMonths) it shows nothing in the formula editor, It should usually show today()+"UserInputValue" . 

I have attached a sample sheet for better understanding.