Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Problem with expression

Hi, I have a function in Excel that i would like to implement in qlikview. In my sheet there's a table as presented below

screen1.PNG.png

And here are the functions:

screen2.PNG.png

I dont have an idea how to calculate expression for active column. I tried to use Above but it didn't really work well :

screen3.PNG.png

as you can see, first row is correct, second also, but in third the value in active should be equal to 1, instead it shows 0 because above function sees 9-9=0 equation and don't remember about this '1' which left from previous row.

Any idea how to fix this ?

1 Solution

Accepted Solutions
MarcoWedel

Hi,

another one creating a calendar table with your date_new and date_finished fields to generate combined week and date fields for both events.

The count of new and finished events per week can then be done using this week and canonical date field:

QlikCommunity_Thread_132254_Pic4.JPG.jpg

QlikCommunity_Thread_132254_Pic2.JPG.jpg

QlikCommunity_Thread_132254_Pic3.JPG.jpg

QlikCommunity_Thread_132254_Pic5.JPG.jpg

QlikCommunity_Thread_132254_Pic8.JPG.jpg

QlikCommunity_Thread_132254_Pic6.JPG.jpg

QlikCommunity_Thread_132254_Pic7.JPG.jpg

tabData:

LOAD RecNo() as ID,

    Date(Date#('01/01/2014', 'MM/DD/YYYY')+Ceil(Rand()*250)) as date_new,

    Date(Date#('01/01/2014', 'MM/DD/YYYY')+Ceil(Rand()*250)) as date_finished

AutoGenerate 100;

tabCalendar:

CrossTable (FieldName, Date)

LOAD * Resident tabData;

Left Join (tabCalendar)

LOAD Distinct

  Date,

  Dual('W'&Num(Week(Date), '00'), WeekStart(Date)) as Week

Resident tabCalendar;

Left Join (tabCalendar)

LOAD Distinct

  FieldName,

  SubField(FieldName, '_', 2) as DateType

Resident tabCalendar;

DROP Field FieldName;

hope this helps

regards

Marco

View solution in original post

3 Replies
nagaiank
Specialist III
Specialist III

Try this script which gives the desired result.

LOAD *,If(IsNull(Peek('active')),new-finished,Peek('active')+new-finished) as active;

LOAD * Inline [

Week,new,finished

W06,6,3

W07,9,3

W08,4,3

W09,9,2

W10,13,6

W11,11,14

];

Capture.PNG.png

MarcoWedel

Hi,

one solution with calculations in the script:

LOAD *,

    Alt(Peek(active),0)+(new-finished) as active

INLINE [

    week, new, finished

    W06, 6, 3

    W07, 9, 3

    W08, 4, 3

    W09, 9, 2

    W10, 13, 6

    W11, 11, 14

];

hope this helps

regards

Marco

MarcoWedel

Hi,

another one creating a calendar table with your date_new and date_finished fields to generate combined week and date fields for both events.

The count of new and finished events per week can then be done using this week and canonical date field:

QlikCommunity_Thread_132254_Pic4.JPG.jpg

QlikCommunity_Thread_132254_Pic2.JPG.jpg

QlikCommunity_Thread_132254_Pic3.JPG.jpg

QlikCommunity_Thread_132254_Pic5.JPG.jpg

QlikCommunity_Thread_132254_Pic8.JPG.jpg

QlikCommunity_Thread_132254_Pic6.JPG.jpg

QlikCommunity_Thread_132254_Pic7.JPG.jpg

tabData:

LOAD RecNo() as ID,

    Date(Date#('01/01/2014', 'MM/DD/YYYY')+Ceil(Rand()*250)) as date_new,

    Date(Date#('01/01/2014', 'MM/DD/YYYY')+Ceil(Rand()*250)) as date_finished

AutoGenerate 100;

tabCalendar:

CrossTable (FieldName, Date)

LOAD * Resident tabData;

Left Join (tabCalendar)

LOAD Distinct

  Date,

  Dual('W'&Num(Week(Date), '00'), WeekStart(Date)) as Week

Resident tabCalendar;

Left Join (tabCalendar)

LOAD Distinct

  FieldName,

  SubField(FieldName, '_', 2) as DateType

Resident tabCalendar;

DROP Field FieldName;

hope this helps

regards

Marco