Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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)
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".
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 🙂
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)
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 🙂
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.
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.
It is being loaded as 'YYYY-MM-DD'. And TODAY() functions work fine with other formulas .
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];
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.