Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
I tried this solution but did not work
How does the date field look like? Screenshot of date field in a list box?
sunny i tried this method and
=Month(Num(Date#(SITUATION_PRODUIT, 'YYYY-MM')))
and it work
You need to extract Month? This works, or you can do just this
=Month(Date#(SITUATION_PRODUIT, 'YYYY-MM'))
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))
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
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
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
thank you sunny
I always take your remarks into consideration
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
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)