topic Re: Cell value in straight table in QlikView

Cell value in straight table

Anonymous — Tue, 03 Apr 2018 09:38:24 GMT

I have a raw data like below table and need below data, could you please show me how to do it. Many thanks

First, top2 AMT from MAX(Date) => 500000 and 70000

Second, "Device Name" of top2 AMT => TT and BB

Third, difference AMT between MAX(Date) and MAX(Date)-1

It should be :

500000 , TT , 493990 (500000-6010)

70000 , BB , 70000 (70000-0)

petter — Tue, 03 Apr 2018 17:18:39 GMT

Like this:

The expression for AMT is:

Sum( {<R#={"=Rank( Sum({<Date={'$(=Date(Max(Date)))'}>} AMT))<=2"}>} AMT )

NOTE: the R# is a field which is created in the load script in this way:

LOAD

RowNo() AS R#,

"Device Name",

.....

......;

The expression for AMT1-AMT2:

Column(1)- Sum( {1<Date={'$(=Date(Max(Date)-1))'},[Device Name]=P({<R#={"=Rank( Sum({<Date={'$(=Date(Max(Date)))'}>} AMT))<=2"}>})>} AMT )

( Column(1) could be replace by the full expression for AMT or even replaced with just AMT )

petter — Fri, 06 Apr 2018 04:55:19 GMT

Please mark the response as answered if this answered your question.