year by year analyse - Qlik Community

sparur · ‎2011-01-24

<body>Hello, Please, give me some advices for fixing my issue... please, look at the qvw-file... short description: i have 3 sites, site could have some different statuses, which can (but not mandatory) change year by year. I want calculate correct numbers of site (count) year by year.... for example, 1) when I select 2008 - I want get such info by status: A - 2 B - 1 C - 0 2) for 2009 -> A-1 B-1 C-1 3) for 2010 -> A-1 B-0 C-2 problem is: I should calculate only the latest rows(by year) for each site, not all transactions... <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt; width: 48pt;" width="64" height="17"></td> <td style="width: 48pt;" width="64"></td> <td style="width: 48pt;" width="64"></td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="right" height="17"></td> <td align="right"></td> <td></td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="right" height="17"></td> <td align="right"></td> <td></td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="right" height="17"></td> <td align="right"></td> <td></td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="right" height="17"></td> <td align="right"></td> <td></td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="right" height="17"></td> <td align="right"></td> <td></td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="right" height="17"></td> <td align="right"></td> <td></td> </tr> </body>

tresesco · ‎2011-01-24

The output you are expecting - does not clear the logic in mind (w.r.t. uploaded application). Can you elaborate the logic a bit more?

regards, tresesco

Anonymous · ‎2011-01-24

I am also not really sure on how you see this, but isn't it a solution to at "site" as an extra dimension?

sparur · ‎2011-01-24

in each year i want count of sites by Status.

and in result I want get this table:

Status/year 2008 2009 2010

A 2 1 1

B 1 1 0

C 0 1 2

site 1 in 2008 had status=A, in 2009 = A (because no transactions with changing of status), in 2010=A (also)

site 2 in 2008 had status=A, in 2009 = B (we have transaction with changing of status), in 2010 = C (we have transaction with changing of status)

site 3 in 2008 had status=B, in 2009 = C (we have transaction with changing of status), in 2010 = C (no transactions with changing of status)

I hope that I described more clear my situation now .

If no, don't hesitate to ask other questions

sparur · ‎2011-01-24

no, I don't need Site as a dimension.

I want two chart:

1) pivot table - as I described it in previous post - 2 dimension: status and Year.

2) pie chart with one dimension - status and user should select one year in list box.

sparur · ‎2011-01-26

No ideas?

Oleg_Troyansky · ‎2011-01-27

i think you need to use advanced aggregation with function aggr and inside it, use firstsortedvalue to get only the last result.

Oleg Troyansky
Call me for expert support, performance tuning, advanced training and more!

tresesco · ‎2011-01-28

Probably this is not the best solution, still if it is what you wanted.... nobody would be happier than me. it took my all yesterday evening to make the logic.

Regards, tresesco