topic Re: cumulative probleme in QlikView

cumulative probleme

souadouert — Mon, 27 Nov 2017 09:29:49 GMT

I have a problem, I have a table has two column ,

1-DATE:date_s

1-Indicateur :

SUM({<date_s,month={"<=$(=month)"},year={"=$(=MAX(year))"},PRODUIT = {"CREDITS"} , NATURE={"CREDIT A LA CONSOMMATION"}>}PRODUCTTION_VOLUME_TND),

indicateur is the it is the cumulative measure from January to the month displayed in the date column

i used this expression but the result is false, after the analyzes I noticed that the result is date = date and not cumulative

Re: cumulative probleme

YoussefBelloum — Mon, 27 Nov 2017 09:45:08 GMT

Hi,

you should use syntax like this:

=sum( {<date_s={">=01/01/2017 <=$(=max(date_s))"}>} PRODUCTTION_VOLUME_TND)

Re: cumulative probleme

souadouert — Mon, 27 Nov 2017 09:49:28 GMT

Hello youssef ,
I tested this expression but nothings changed because i have the date_s in the colunm So , the indicateur folow this date

Re: cumulative probleme

YoussefBelloum — Mon, 27 Nov 2017 09:54:07 GMT

try just to test it (add it on a textbox for example)

if yo have data like this for examples:

Date, Mois, Indicateur

01/01/2017, 01, 100

01/02/2017, 02, 300

16/03/2017, 03, 400

27/04/2017, 04, 150

if you select 16/03/2017 on the date listbox, you will have Sum = 800

if you select 01/02/2017 on the date listbox, you will have Sum = 400

Re: cumulative probleme

avkeep01 — Mon, 27 Nov 2017 09:54:29 GMT

Hi Souad,

You could try:

RANGESUM(ABOVE(SUM({$<month={"<=$(=month)"},year={"=$(=MAX(year))"},PRODUIT = {"CREDITS"} , NATURE={"CREDIT A LA CONSOMMATION"}>}PRODUCTTION_VOLUME_TND),0,ROWNO(TOTAL)))

Re: cumulative probleme

souadouert — Mon, 27 Nov 2017 09:56:31 GMT

my date FORMAT is MM/YYYY,

Re: cumulative probleme

souadouert — Mon, 27 Nov 2017 09:56:44 GMT

it can causes problems

Re: cumulative probleme

souadouert — Mon, 27 Nov 2017 09:59:00 GMT

hello, van keep

this expression doesnt satisfaited my needs

Re: cumulative probleme

YoussefBelloum — Mon, 27 Nov 2017 10:00:31 GMT

no problem:

=sum( {<Date={">=01/2017 <=$(=max(Date))"}>} Indicateur)

Re: cumulative probleme

souadouert — Mon, 27 Nov 2017 10:03:40 GMT

Yes, i aleardy tested but no thing changed the problem is with th column date_s when i deleted , the indicator is true

Re: cumulative probleme

YoussefBelloum — Mon, 27 Nov 2017 10:32:56 GMT

Try to share sample data or an app with the same problem

Re: cumulative probleme

avkeep01 — Mon, 27 Nov 2017 10:39:12 GMT

Hi Souad,

I assumed that Date_s was a dimension in your chart. That's why I used another expression. As you mentioned below that isn't working properly. Maybe you could use TOTAL in your function, maybe with TOTAL <month>.

Like: sum( TOTAL {<Date={">=01/2017 <=$(=max(Date))"}>} Indicateur)

Re: cumulative probleme

souadouert — Mon, 27 Nov 2017 10:46:57 GMT

yes Van i used total , it works but , whe i selected two date for exemple 04/2017 and 03/2017
--->03/2017 is not true

Re: cumulative probleme

avkeep01 — Mon, 27 Nov 2017 10:55:53 GMT

Hi Souad,

Have you tried:

sum( TOTAL <month>{<Date={">=01/2017 <=$(=max(Date))"}>} Indicateur)

Re: cumulative probleme

souadouert — Mon, 27 Nov 2017 11:06:51 GMT

the issue now is with MAX (date)

when i select april and mars so the max date is 04/2017 and it sum the cumul to april

Re: cumulative probleme

YoussefBelloum — Mon, 27 Nov 2017 11:22:24 GMT

first you said that:

whe i selected two date for exemple 04/2017 and 03/2017

--->03/2017 is not true

and also:

when i select april and mars so the max date is 04/2017 and it sum the cumul to april (which is not true also)

so can you tell what you want if you select 03/2017 and 04/2017, if you have data like this ?

date_s, PRODUCTTION_VOLUME_TND

01/01/2017, 100

01/02/2017, 300

16/03/2017, 400

27/04/2017, 150

Re: cumulative probleme

souadouert — Mon, 27 Nov 2017 11:28:26 GMT

01/01/2017 100

01/02/2017, 400

16/03/2017, 800

27/04/2017, 950

Re: cumulative probleme

avkeep01 — Mon, 27 Nov 2017 11:31:42 GMT

Hi Souad, in this simplified example the following solution works:

RANGESUM(ABOVE(SUM(PRODUCTTION_VOLUME_TND),0,ROWNO(TOTAL)))

Re: cumulative probleme

sunny_talwar — Mon, 27 Nov 2017 12:00:18 GMT

What is wrong with it? Does it give you incorrect result or no result?

Re: cumulative probleme

souadouert — Mon, 27 Nov 2017 12:24:57 GMT

tOTAL FUNCTION IGNORE THE COLOUMN IN THE TABLE FOR EXEMPLE I ADDED AGENCE CLOMUN TOTAL , CALCULATED THE TOTAL AGENCE , So i need just calculated the cumulative indicateur for each agence