topic Re: calculation in QlikView

calculation

buzzy996 — Fri, 30 Oct 2015 10:23:35 GMT

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

Re: calculation

jonathandienst — Fri, 30 Oct 2015 11:01:59 GMT

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.

Re: calculation

buzzy996 — Fri, 30 Oct 2015 11:29:31 GMT

Hi Jonathan,

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

Re: calculation

Gysbert_Wassenaar — Fri, 30 Oct 2015 12:02:14 GMT

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);

Re: calculation

buzzy996 — Fri, 30 Oct 2015 16:11:54 GMT

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.