topic Re: cumulative date in QlikView

cumulative date

souadouert — Fri, 31 Mar 2017 10:15:15 GMT

Good morning

I have a cross table, I want when I select a date, a column displays the realization --> the date of selection and a column displays the cumulative from the beginning 2017 until the selected date

thank you

Re: cumulative date

Anil_Babu_Samineni — Fri, 31 Mar 2017 10:18:06 GMT

May be this?

Sum({<date = {">=Yearstart(date) <=Max(date)"}>} Sales)

Re: cumulative date

souadouert — Fri, 31 Mar 2017 10:32:36 GMT

anil i want compare the year of date selection >2017 until the selected date

Re: cumulative date

Anil_Babu_Samineni — Fri, 31 Mar 2017 10:42:07 GMT

May be i am not follow you, Would you provide more input on this. Better with few rows and expected result to see while selection happen

Re: cumulative date

sunny_talwar — Fri, 31 Mar 2017 10:42:36 GMT

Are you looking to get YTD current year? What is your chart dimension?

Re: cumulative date

souadouert — Fri, 31 Mar 2017 10:47:30 GMT

for exemple sunny i selected fev 2017 , so a column displays the realisation (fevr 2017) and other column (janv+fevrier 2017)

Re: cumulative date

sunny_talwar — Fri, 31 Mar 2017 10:50:12 GMT

So, MonthYear is not a dimension? You want two expressions, 1 which gives the current monthyear and the other which is YTD (Year-to-date)?

Re: cumulative date

souadouert — Fri, 31 Mar 2017 12:55:36 GMT

I will try with your method

Re: cumulative date

souadouert — Fri, 31 Mar 2017 13:11:59 GMT

hello sunny

How to extract the month of a date as yyyy-mm??

Re: cumulative date

sunny_talwar — Fri, 31 Mar 2017 13:13:07 GMT

Like this

Date(MonthStart(DateField), 'YYYY-MM')

Re: cumulative date

souadouert — Fri, 31 Mar 2017 13:15:49 GMT

I tried this solution but did not work

Re: cumulative date

sunny_talwar — Fri, 31 Mar 2017 13:17:11 GMT

How does the date field look like? Screenshot of date field in a list box?

Re: cumulative date

souadouert — Fri, 31 Mar 2017 13:20:09 GMT

sunny i tried this method and

=Month(Num(Date#(SITUATION_PRODUIT, 'YYYY-MM')))

and it work

Re: cumulative date

souadouert — Fri, 31 Mar 2017 13:21:42 GMT

Re: cumulative date

sunny_talwar — Fri, 31 Mar 2017 13:27:03 GMT

You need to extract Month? This works, or you can do just this

=Month(Date#(SITUATION_PRODUIT, 'YYYY-MM'))

Re: cumulative date

souadouert — Fri, 31 Mar 2017 14:01:32 GMT

Sunny i

I used this expression, you think it is a solution does not generate problems later

=sum(if(Year(Date#(getfieldselections(SITUATION_PRODUIT), 'YYYY-MM')) ='2016'and MONTH(Date#(getfieldselections(SITUATION_PRODUIT),'YYYY-MM')) <=Month(Date#(SITUATION_PRODUIT, 'YYYY-MM')), REAL_PACKS.NOMBRE_PACK))

Re: cumulative date

sunny_talwar — Fri, 31 Mar 2017 14:09:37 GMT

I would suggest fixing SITUATION_PRODUIT in the script and calculate Year and Month from it... I don't like to make a lot of front end modifications

Why don’t my dates work?

QlikView Date fields‌

Get the Dates Right‌

Next I feel that you can probably use set analysis instead of the if statement, but not sure here because of your complex data model

Dates in Set Analysis

Finally, you hard coded 2016, is this needed to change every year? I don't like to hard code things, but if it never changes, then I think you are all good

Re: cumulative date

souadouert — Fri, 31 Mar 2017 14:23:23 GMT

thank you sunny

I always take your remarks into consideration

Re: cumulative date

souadouert — Fri, 31 Mar 2017 15:11:26 GMT

I modified my script

DATES:

LOAD

SITUATION_PRODUIT,

MONTH((Date#(SITUATION_PRODUIT,'YYYY-MM'))) as MONTH,

Year(Date#(SITUATION_PRODUIT,'YYYY-MM')) as YEAR

FROM

(ooxml, embedded labels, table is Feuil1);

and now i have to change expression So i used this expresion what do you think ?

==sum( {<YEAR = {"2017"}, MONTH ={"<= max(MONTH)"}>} REAL_CAPITALCLIENT.NOMBRE_CLIENT)

so i need to sum the values between January and the month of the selected date

Re: cumulative date

sunny_talwar — Fri, 31 Mar 2017 15:15:58 GMT

This or you can try this

DATES:

LOAD SITUATION_PRODUIT,

MONTH(SITUATION_PRODUIT) as MONTH,

Num(MONTH(SITUATION_PRODUIT)) as MONTHNUM,

Year(SITUATION_PRODUIT) as YEAR;

LOAD Date(Date#(SITUATION_PRODUIT,'YYYY-MM')) as SITUATION_PRODUIT

FROM

(ooxml, embedded labels, table is Feuil1);

and then this

=Sum({<YEAR = {'2017'}, MONTH, MONTHNUM = {"<= $(=Max(MONTHNUM))"}>} REAL_CAPITALCLIENT.NOMBRE_CLIENT)

=Sum({<YEAR = {$(=Max(YEAR))}, MONTH, MONTHNUM = {"<= $(=Max(MONTHNUM))"}>} REAL_CAPITALCLIENT.NOMBRE_CLIENT)