Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
souadouert
Specialist
Specialist

cumulative date

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

20 Replies
souadouert
Specialist
Specialist
Author

I tried this solution but did not work

sunny_talwar

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

souadouert
Specialist
Specialist
Author

sunny i tried this method and

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

and it work

souadouert
Specialist
Specialist
Author

date.PNG

sunny_talwar

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

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

souadouert
Specialist
Specialist
Author

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))

sunny_talwar

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

souadouert
Specialist
Specialist
Author

thank you sunny

I always take your remarks into consideration

souadouert
Specialist
Specialist
Author

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

sunny_talwar

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)

or

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