calculation

[buzzy996]

Hi Guys,

Can some one help to get the expected output column calculation in the attached file?

Note:If it's possible the calculation should be from front end(chart level) else from load statement(I'm trying with previous/peek functions) but data is not in sequential manner(especially the status),so i'm not getting expected output.

Thanks

[jonathandienst]

I suggest that you load the data into a qlikview document at least, and then upload the qvw file rather than just the spreadsheet. You can still include the spreadsheet if the question relates to the loading of the data. I don't have time to build the app from scratch myself, and  that probably applies to other people chow could potentially help you.

[buzzy996]

Hi Jonathan,

Sample is attached and ExpectedOutput and required conditions are mentioned in the attached excel file. !

[Gysbert_Wassenaar]

This should do it:

Table:

LOAD *, If(STATUS='Awaiting Reply',floor(LASTCHANGEDTS)-alt(CalcDate,floor(CREATETS))) as OutPut;

LOAD TASK_REF,

    TASK_VERSION_KEY,

    VERSIONID,

    STATUS,

    CREATETS,

    LASTCHANGEDTS,

    ExpectedOutPut,

    [Conditions:],

    If(previous(STATUS)='Awaiting Reply' and STATUS='Reply Received',floor(LASTCHANGEDTS),Peek('CalcDate')) as CalcDate

FROM [comm189784.xlsx] (ooxml, embedded labels, table is Data);

[buzzy996]

Hi Gysbert,

Many Thanks for your above script,that is working only for one case which I shared earlier above example.

but i tried to apply the above script for few more case that 's not working,because of my data is very dynamic.

can u look into the attached cases & help?

FYI, Actually i'm assuming for calculation even data is dynamic--->

For same ticket,

Case 1: First time status as "Awaiting Reply": Outbound emails (Awaiting Reply) - CreatedDate

Case 2: if status as "Awaiting Reply" 2nd time: Outbound emails 2nd time (Awaiting Reply)- Inbound-emails 1'st Time (Reply received)

..etc

For me it's challenging right now, could you assist?

Thank you.