Max Month per Quarter in set analysis if sum(value) not null

[lessassy]

Hello everyone,

I have these datas

I would like to display the sum of value when taking into account the max month of a quarter when there is a value.

Basically for each quarter i would like the max month when value is not null

Basically it would be something like that :

if (Quarter = 'Q1' ,SUM({<CAT_CONTRAT = {'CDI'}, Annee = {'$(=max(Year)-1)'} ,
Month = {'???????????'}>}Value),

if (Quarter = 'Q2',SUM({<CAT_CONTRAT = {'CDI'}, {'$(=max(Year)-1)'} ,
Month = {'???????????'}>}Value),

if (Quarter = 'Q3' ,SUM({<CAT_CONTRAT = {'CDI'}, {'$(=max(Year)-1)'} ,
Month = {'???????????'}>}Value),

if (Quarter = 'Q4' ,SUM({<CAT_CONTRAT = {'CDI'}, Annee = {'$(=max(Year)-1)'} ,
Month = {'???????????'}>}Value),))))

Thank you

[vamsee · Accepted solution]

May be try something like

for Value

Only(Aggr(Max({<Value={">0"},CAT_CONTRAT={'CDI'}>}Value),Quarter, Year))

For Month Name

Month(Date#(Aggr(Max({<Value={">0"},CAT_CONTRAT={'CDI'}>}Month),Quarter, Year)
, 'M'))

If this doesn't work let us know the dimensions you are using or expected output.

[lessassy]

Hello,

It worked well thank you !

[vamsee]

Glad could be of help.

---------------------------------------

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.