Comparate sum two years without values zero in months

[olramirez]

Hello,

how could i comparate 2 years with cathegories don´t have zero in yours months.

For example:

Client	Month	2013	2014
Client1	Ene	2	7
Client1	Feb	2	1
Client1	Mar	3	5
Client1	Abr	4	3
Client1	May	3	9
Client1	Jun	3	9
Client1	Jul	8	0
Client1	Ago	9	0
Client1	Sep	3	0
Client1	Oct	2	0
Client1	Nov	7	0
Client1	Dic	1	0
Client2	Ene	7	9
Client2	Feb	2	3
Client2	Mar	2	5
Client2	Abr	1	3
Client2	May	4	1
Client2	Jun	7	3
Client2	Jul	9	4
Client2	Ago	1	4
Client2	Sep	2	8
Client2	Oct	8	4
Client2	Nov	5	9
Client2	Dic	2	8
Client3	Ene	5	7
Client3	Feb	6	7
Client3	Mar	8	7
Client3	Abr	6	8
Client3	May	6	6
Client3	Jun	9	5
Client3	Jul	9	9
Client3	Ago	4	10
Client3	Sep	3	5
Client3	Oct	0	10
Client3	Nov	0	5
Client3	Dic	0	8
Client4	Ene	5	7
Client4	Feb	6	7
Client4	Mar	8	7
Client4	Abr	6	8
Client4	May	6	6
Client4	Jun	9	5
Client4	Jul	9	9
Client4	Ago	4	10
Client4	Sep	3	5
Client4	Oct	4	10
Client4	Nov	1	5
Client4	Dic	1	8

I  want Qlikview show the YTD Client2 and Client4

Client	2013	2014
Client2	50	61
Client4	62	87

Is this posible?

[Anonymous]

Does the attached give you food for thought :

[sujeetsingh]

You can exclude zero values while loading.

[olramirez]

Thanks Bill,

I need a set analysis or any expression where i can find Client2 and Client4.  The example have just 4 clients but i have 200 clients currently.

If any client had zero in any month, that client can't be take it in the YTD.

Thanks again.

[olramirez]

Thanks sujeetsingh,

but If any client had zero in any month, that client can't be take it in the YTD.

[Anonymous]

Would something like this be better ?

[petter]

In your result it seems like you will not want clients that have any zero month for the two years up until the YTD month - right? The formula I suggest takes that into consideration.

[olramirez]

Hello Peter,

could you help to me when the table is like this:

Year	Month	Client	Value
2013	Jan	Client1	5
2013	Feb	Client1	10
2013	Mar	Client1	1
2013	Apr	Client1	1
2013	May	Client1	3
2013	Jun	Client1	3
2013	Jul	Client1	6
2013	Aug	Client1	9
2013	Sep	Client1	1
2013	Oct	Client1	6
2013	Nov	Client1	9
2013	Dec	Client1	3
2013	Jan	Client2	1
2013	Feb	Client2	3
2013	Mar	Client2	7
2013	Apr	Client2	3
2013	May	Client2	2
2013	Jun	Client2	4
2013	Jul	Client2	6
2013	Aug	Client2	2
2013	Sep	Client2	2
2013	Oct	Client2	6
2013	Nov	Client2	7
2013	Dec	Client2	2
2013	Jan	Client3	8
2013	Feb	Client3	2
2013	Mar	Client3	1
2013	Apr	Client3	6
2013	May	Client3	6
2013	Jun	Client3	9
2013	Jul	Client3	10
2013	Aug	Client3	7
2013	Sep	Client3	1
2013	Oct	Client3	0
2013	Nov	Client3	0
2013	Dec	Client3	0
2013	Jan	Client4	1
2013	Feb	Client4	2
2013	Mar	Client4	1
2013	Apr	Client4	10
2013	May	Client4	3
2013	Jun	Client4	7
2013	Jul	Client4	10
2013	Aug	Client4	3
2013	Sep	Client4	3
2013	Oct	Client4	4
2013	Nov	Client4	1
2013	Dec	Client4	8
2014	Jan	Client1	3
2014	Feb	Client1	7
2014	Mar	Client1	8
2014	Apr	Client1	9
2014	May	Client1	10
2014	Jun	Client1	9
2014	Jul	Client1	7
2014	Aug	Client1	8
2014	Sep	Client1	7
2014	Oct	Client1	7
2014	Nov	Client1	0
2014	Dec	Client1	0
2014	Jan	Client2	2
2014	Feb	Client2	10
2014	Mar	Client2	5
2014	Apr	Client2	10
2014	May	Client2	4
2014	Jun	Client2	5
2014	Jul	Client2	4
2014	Aug	Client2	10
2014	Sep	Client2	8
2014	Oct	Client2	1
2014	Nov	Client2	9
2014	Dec	Client2	3
2014	Jan	Client3	1
2014	Feb	Client3	5
2014	Mar	Client3	6
2014	Apr	Client3	7
2014	May	Client3	3
2014	Jun	Client3	3
2014	Jul	Client3	2
2014	Aug	Client3	1
2014	Sep	Client3	6
2014	Oct	Client3	10
2014	Nov	Client3	4
2014	Dec	Client3	6
2014	Jan	Client4	4
2014	Feb	Client4	1
2014	Mar	Client4	4
2014	Apr	Client4	2
2014	May	Client4	9
2014	Jun	Client4	8
2014	Jul	Client4	7
2014	Aug	Client4	2
2014	Sep	Client4	3
2014	Oct	Client4	9
2014	Nov	Client4	9
2014	Dec	Client4	10

We had to do changes.

i have tried to adapt your analysis but this look imposible, please.

[olramirez]

Hello Bill,

could you help to me when the table is like this:

Year	Month	Client	Value
2013	Jan	Client1	5
2013	Feb	Client1	10
2013	Mar	Client1	1
2013	Apr	Client1	1
2013	May	Client1	3
2013	Jun	Client1	3
2013	Jul	Client1	6
2013	Aug	Client1	9
2013	Sep	Client1	1
2013	Oct	Client1	6
2013	Nov	Client1	9
2013	Dec	Client1	3
2013	Jan	Client2	1
2013	Feb	Client2	3
2013	Mar	Client2	7
2013	Apr	Client2	3
2013	May	Client2	2
2013	Jun	Client2	4
2013	Jul	Client2	6
2013	Aug	Client2	2
2013	Sep	Client2	2
2013	Oct	Client2	6
2013	Nov	Client2	7
2013	Dec	Client2	2
2013	Jan	Client3	8
2013	Feb	Client3	2
2013	Mar	Client3	1
2013	Apr	Client3	6
2013	May	Client3	6
2013	Jun	Client3	9
2013	Jul	Client3	10
2013	Aug	Client3	7
2013	Sep	Client3	1
2013	Oct	Client3	0
2013	Nov	Client3	0
2013	Dec	Client3	0
2013	Jan	Client4	1
2013	Feb	Client4	2
2013	Mar	Client4	1
2013	Apr	Client4	10
2013	May	Client4	3
2013	Jun	Client4	7
2013	Jul	Client4	10
2013	Aug	Client4	3
2013	Sep	Client4	3
2013	Oct	Client4	4
2013	Nov	Client4	1
2013	Dec	Client4	8
2014	Jan	Client1	3
2014	Feb	Client1	7
2014	Mar	Client1	8
2014	Apr	Client1	9
2014	May	Client1	10
2014	Jun	Client1	9
2014	Jul	Client1	7
2014	Aug	Client1	8
2014	Sep	Client1	7
2014	Oct	Client1	7
2014	Nov	Client1	0
2014	Dec	Client1	0
2014	Jan	Client2	2
2014	Feb	Client2	10
2014	Mar	Client2	5
2014	Apr	Client2	10
2014	May	Client2	4
2014	Jun	Client2	5
2014	Jul	Client2	4
2014	Aug	Client2	10
2014	Sep	Client2	8
2014	Oct	Client2	1
2014	Nov	Client2	9
2014	Dec	Client2	3
2014	Jan	Client3	1
2014	Feb	Client3	5
2014	Mar	Client3	6
2014	Apr	Client3	7
2014	May	Client3	3
2014	Jun	Client3	3
2014	Jul	Client3	2
2014	Aug	Client3	1
2014	Sep	Client3	6
2014	Oct	Client3	10
2014	Nov	Client3	4
2014	Dec	Client3	6
2014	Jan	Client4	4
2014	Feb	Client4	1
2014	Mar	Client4	4
2014	Apr	Client4	2
2014	May	Client4	9
2014	Jun	Client4	8
2014	Jul	Client4	7
2014	Aug	Client4	2
2014	Sep	Client4	3
2014	Oct	Client4	9
2014	Nov	Client4	9
2014	Dec	Client4	10

sorry, but we had to do changes.

Please help me

[petter]

Hi Olga, not so simple - but quite doable: