topic Re: Apply logic as expression in Straight table without Peek and Previous in QlikView

Apply logic as expression in Straight table without Peek and Previous

aj0031724 — Thu, 27 Aug 2015 14:11:29 GMT

Hi,

I am trying to modify the below script to use in Straight Table AS EXPRESSION but not getting an idea how Can I apply the same logic without Peek and Previous as these are not working in Straight Table :

if(Previous(Value)>4 and Value>4,PROFIT,
if(-rangesum((Value>4),(peek(Value,-1)>4),(peek(Value,-2)>4),(peek(Value,-3)>4),(peek(Value,-4)>4),(peek(Value,-5)>4),(peek(Value,-6)>4))>=4,'Highly PROFIT,
if(-rangesum((Value<3),(peek(Value,-1)<3),(peek(Value,-2)<3),(peek(Value,-3)<3),(peek(Value,-4)<3),(peek(Value,-5)<3),(peek(Value,-6)<3))>=6,'Low PROFIT',
'LOSS''))) as CLASSES.

I AM USING DATE AS DIMENSION.

THE REQUIREMNT IS TO MARK EACH DATE WITH CLASSIFICATION AS ABOVE DOING 7 DAYS SLIDING WINODWS CALCULATION.

The 7 day logic means a cell is classified according to if in the last 7 days the classification logic comes out TRUE. So for each day you must look at the 6 days previous to the day + the day considered (total of 7 days) and then do the logic and decide which classification is TRUE for that day.

Can you please advice/suggest the workaround.

Re: Apply logic as expression in Straight table without Peek and Previous

aj0031724 — Fri, 28 Aug 2015 10:27:51 GMT

Dear Team,

Can you please help me on this?

Re: Apply logic as expression in Straight table without Peek and Previous

Fri, 28 Aug 2015 10:36:07 GMT

The first thing it already working fine on the script, what is the purpose of doing same on the chart. If you keep on the script its would be improve the UI performance.

If you need to do in UI chart, add Value to dimension & use above function.

Re: Apply logic as expression in Straight table without Peek and Previous

Fri, 28 Aug 2015 10:36:41 GMT

Please post qvw then we can understand data model & suggest the solution.

Re: Apply logic as expression in Straight table without Peek and Previous

Digvijay_Singh — Fri, 28 Aug 2015 11:00:35 GMT

PROFIT in first line is without quote, Highly Profit in third line is with starting quote only and Low Profit is with both starting and ending quote. Are you sure this script is working? I found interesting sections in rangesum function so was trying to run with date and value inline fields first in script. Also parenthesis are not matching. I thought of converting it to charts using Above but not able to understand the source. Please comment.

Thanks,

Digvijay

Re: Apply logic as expression in Straight table without Peek and Previous

jonathandienst — Fri, 28 Aug 2015 11:09:28 GMT

I am afraid that your expression is faulty and it is not clear (to me at least) exactly how it should work. I suggest that you post your qvw or a representative example, and/or some example Value data and the required output.

Re: Apply logic as expression in Straight table without Peek and Previous

aj0031724 — Fri, 28 Aug 2015 11:25:58 GMT

Hi All,

Apologies for inconvenience due to syntax error.

I will consolidate the requirement with an example and share .

Re: Apply logic as expression in Straight table without Peek and Previous

aj0031724 — Mon, 31 Aug 2015 10:51:13 GMT

Dear Team,

PFB the requirement with example:

One Table Structure Of KPI:

KPINAME KPIFORMULA
A SUM(10) + SUM(20)

B SUM(30)+ SUM(40)

Note: IN our dashboard KPI value gets evelauted at run time through use of filter selction of KPI WITH VARIABLE AS BELOW EXAMPLE:

vkpievaluation=MaxString({$<KPINAME={'$(vSelectedKpi1)'}>} KPIFORMULA)

and we use $(vkpievaluation) in an expression to get the value of KPI.

IF USER SEELCTS kpiname->A KPIVALUE-->$(vkpievaluation)=30.

AnOTHER TABLE STRUCTURE OF CELL WITH DATE AS EXAMPLE:

DATE CELL

01/08/2015 C1

05/08/2015 C1

10/08/2015 C1

15/08/2015 C1

20/8/2015 C1

25/08/2015 C1

31/08/2015 C1

01/08/2015 C2

05/08/2015 C2

10/08/2015 C2

15/08/2015 C2

20/8/2015 C2

25/08/2015 C2

31/08/2015 C2.

Requirement:

MARK EACH DATE WITH BELOW CLASSIFICATION BY DOING 7 DAYS SLIDING WINDOW.

CASSIFICATION LOGIC:

Alarmed -->    last 2 days above 4(calculated KPI value)
High -->            more than 3 occurences above 4 (4 or more occurences above 4)
Standard--->    (3 or less occurences above 4) AND (5 or less occurences below 3)
Low Utilized ---> more than 5 occurences below 3 (6 or more occurences below 3)

Suppose User Filter is as below:

a)KPI NAME-> A

B) Date-> 31/08/2015

SO dashboard output requirement:
Per KPI chosen from the KPI list (user may choose multiple KPIs) and the cells inside the current filter show the following:
a. Pie Chart of percentage breakdown to Alarmed Cells, Highly Utilized Cells, Standard Cells and Low Utilized cells
(Alarmed, High and Low - if there is a KPI from the filtered KPI list which is Alarmed High or Low).

b) Straight TABLE OUTPUT WITH BELOW COLUMNS:

DATE        KPI    CELL VALUE          CLASSIFICCATIONS
31/08/2015 A    C1 $(vkpievaluation) ALARMED (IF THE EVALATED KPIVALUE FALLS IN THIS RANGE)
31/08/2015 A    C2 $(vkpievaluation) HIGH ((IF THE EVALATED KPIVALUE FALLS IN THIS RANGE).

I was using the below script but since I don't know in advance the KPI value for sector/cell ,I need some workaround to get the Classification at run time.

if(Previous(Value)>4 and Value>4,'Alarmed',
if(-rangesum((Value>4),(peek(Value,-1)>4),(peek(Value,-2)>4),(peek(Value,-3)>4),(peek(Value,-4)>4),(peek(Value,-5)>4),(peek(Value,-6)>4))>=4,'Highly utilized',
if(-rangesum((Value<3),(peek(Value,-1)<3),(peek(Value,-2)<3),(peek(Value,-3)<3),(peek(Value,-4)<3),(peek(Value,-5)<3),(peek(Value,-6)<3))>=6,'Low utilized',
'Standard'))) as Classification,

Please advice/suggest .

Re: Apply logic as expression in Straight table without Peek and Previous

aj0031724 — Tue, 01 Sep 2015 09:40:41 GMT

Team,

Can you please advice/suggest?

Re: Apply logic as expression in Straight table without Peek and Previous

aj0031724 — Wed, 02 Sep 2015 07:33:43 GMT

Team,

Can you please advice as per above request?

Re: Apply logic as expression in Straight table without Peek and Previous

jonathandienst — Wed, 02 Sep 2015 07:46:03 GMT

My suggestion was to post a qvw file with representative data and the objects which you want to populate, as well as the expected output for these objects...

Re: Apply logic as expression in Straight table without Peek and Previous

aj0031724 — Wed, 02 Sep 2015 09:53:16 GMT

Dear Jonathan,

PFA the qvw with data ,variables and formulas and the expected output.

Re: Apply logic as expression in Straight table without Peek and Previous

aj0031724 — Thu, 03 Sep 2015 09:17:26 GMT

Team,

Can you please advice as per the qvw attached?