topic Re: Find difference between 2 values across a certain date range in QlikView

Find difference between 2 values across a certain date range

NewbieToQV — Thu, 18 Jan 2024 11:42:29 GMT

Hi, I need help on the below.

I have grades of some students and need to know if they improved on the results based on the latest 2 tests. (i.e. I need the result tab in yellow).

I am on QlikView and appreciate your assistance. Many thanks.

Re: Find difference between 2 values across a certain date range

rwunderlich — Thu, 18 Jan 2024 20:32:00 GMT

What does you data table look like when loaded into QV? Can you post a screenshot of the table model?

-Rob

Re: Find difference between 2 values across a certain date range

Saravanan_Desingh — Fri, 19 Jan 2024 02:44:05 GMT

Try something like this,

tab1: CrossTable(Month, Grade) LOAD * INLINE [ ID, 1/1/23, 2/1/23, 3/1/23, 4/1/23, 5/1/23, 6/1/23, 7/1/23, 8/1/23 1000, , , A, B, Pass, , , 1001, , , , , , , D, 1002, , , , , , D, , A 1003, A, B, B, , , , , ]; Left Join(tab1) LOAD ID, Concat(If(Not Len(Trim(Grade))=0,Grade&'@'),'',Date#(Month)) As C1 Resident tab1 Group By ID; Left Join(tab1) LOAD ID, SubField(C1,'@',-3) &'->'& SubField(C1,'@',-2) As Rationale, If(SubField(C1,'@',-2) > If(SubField(C1,'@',-3)='Pass','Z',SubField(C1,'@',-3)), 'Deteriorate', If(SubField(C1,'@',-2) < SubField(C1,'@',-3), 'Improve', 'n/a')) As Result Resident tab1;

Re: Find difference between 2 values across a certain date range

NewbieToQV — Fri, 19 Jan 2024 04:26:56 GMT

Thanks!

I don't have access to the script. Any solution to do at the chart/table properties?