topic Re: Problem with max(date) in a chart in QlikView

Problem with max(date) in a chart

Wed, 02 Apr 2014 13:40:29 GMT

Hello

I met an problem i explain you,

I want to sum an indicator for every month only at the max(date_chargement) of the month wich is not always the same with the end of the date of the calendar month.

Date_chargement is an field wich permit us to know the date of the loading…

My dimension is the month ….

I ve try two expression in an chart but I don’t know why it doesn’t work.

1) First expression

Sum({$<Division={$(=TxtSociété)},
Year={$(=vYearMax)},,Month=,Période=,[date_chargement]={">=$(=Max([date_chargement])) <=$(=Max([date_chargement]))"}>}[Indicateur])

This expression give an result only for the current month and as you can look under nothing for the others months.

2) Second expression

Sum({$<Division={$(=TxtSociété)},
Year={$(=vYearMax)},Month=,Période=,[date_chargement]={">=$(=Max([Date])) <=$(=Max([Date]))"}>}[ Indicateur])

Is somebody could help me to fix this problem..

Thanks

Re: Problem with max(date) in a chart

its_anandrjs — Wed, 02 Apr 2014 13:47:38 GMT

Try first statement like

Sum({$<Division={$(=TxtSociété)},
Year={$(=vYearMax)},Month=,Période=,[date_chargement]={">=$(=Max([date_chargement])) <=$(=Max([date_chargement]))"}>}[Indicateur])

Re: Problem with max(date) in a chart

alexandros17 — Wed, 02 Apr 2014 13:48:00 GMT

at first

[date_chargement]={">=$(=Max([date_chargement])) <=$(=Max([date_chargement]))"

is equivalent to

[date_chargement]={"$(=Max([date_chargement])"} but the real problem is that

Max([date_chargement])is the max among ALL your [date_chargement] values.

To resolve the question you have to compute max for each month in the script (with a left join on the key) assigning a flag to max value for each month.

Re: Problem with max(date) in a chart

Gysbert_Wassenaar — Wed, 02 Apr 2014 13:50:37 GMT

Set analysis expressions calculate a set for the entire chart, not per row (or column in your pivot table). You'll have to use if statements instead. Perhaps something like sum({<.....>} if(Date = aggr(max(date_changement),Month),[Indicateur],0))