topic [ASK] how to make a limiter with variable ? in QlikView

[ASK] how to make a limiter with variable ?

Mon, 26 Jul 2010 11:41:24 GMT

hello..

i have another problem

i want to show sum of all the data below max date

i've tried to make variable Vmax_cicildate_cad, which is the containt is :

max(date(cicildate_CAD))

and i tried to create a chart that contain a field sum of datas below variable Vmax_cicildate_cad

and the script is :

sum( if(date(cicildate_CAD)<=Vmax_cicildate_cad,

if(closetype_CA=0 and paidsts_CAD=0, Num#(angsuran_CAD ))

))

when i didn't choose any date, it worked

just like the pic below :

field "nilai" is the one

and i also made 1 list box for dates :

but when i select one of the dates like this :

it shows data like this :

"Jumlah Konsumen" and "Nilai" show as not i expected

when i tried on sql server with syntax like this :

SELECT count(custid),SUM(coraccountdetail.angsuran)

FROM coraccount

INNER JOIN coraccountdetail ON coraccount.norek = coraccountdetail.norek AND coraccount.nopin = coraccountdetail.nopin

WHERE coraccount.closetype = 0

AND coraccountdetail.paidsts = 0

AND coraccountdetail.cicildate < ('09/12/2005')

the result for "Jumlah Konsumen" is 491 and "Nilai" is 94040600

---

so in the qlikview it only shows the data of current date that i select ( date 12 - month 9 - year 2005)

but in sql server it shows all the data before month 9- date 12 - year 2005

how is it could happen?

and how to make qv shows all the data below the date that i select?

many thx...

🙂

[ASK] how to make a limiter with variable ?

jonathandienst — Mon, 26 Jul 2010 11:57:01 GMT

Its because the selections you make (in this case date) are applied before your exprssion is calculated. In other words, when you select a date, your dataset is limited to the records that match the date selection.

You can override the selection using set analysis.

Jonathan

[ASK] how to make a limiter with variable ?

Mon, 26 Jul 2010 12:34:57 GMT

@Jo

owh thx Jo

but how to do that?

[ASK] how to make a limiter with variable ?

Mon, 26 Jul 2010 16:44:59 GMT

i've tried with :

=sum({$<cicildate_CAD={$(=Max(date(cicildate_CAD))), $(=Max(date(cicildate_CAD)))}>} angsuran_CAD)

but it doesn't work

i want to sum all the value below the date i select

i dont know how to figure this out

need help really

thx..

[ASK] how to make a limiter with variable ?

jonathandienst — Mon, 26 Jul 2010 17:27:33 GMT

=sum({$<cicildate_CAD={"<=$(=Date(Max(cicildate_CAD)))"}>} angsuran_CAD)

That should sum all records where cicildate_CAD is less than the maximum of the currently selection of cicildate_CAD. If you have other date fields linked to cicildate_CAD, you may need to override them; something like cicildate_Month=,cicildate_YEAR=

Jonathan

[ASK] how to make a limiter with variable ?

Tue, 27 Jul 2010 08:34:32 GMT

@Jo

thx a lot Jo for ur help

it's sccess

🙂