compare weekly sales using different colors

[paolojolly]

Hello,
I need to compare weekly sales of the current year with those of the previous one and show data using different colors:

this year's sales using green color
last year's sales up to the current week using orange
last year's sales from the current week using yellow

and I don't want 52, 53, 54 weeks that don't have data

can someone help me?

Thanks!

[Taoufiq_Zarra]

attached new version

i replace If ... with Set Analysis

[paolojolly]

Thank you for your response, it helps me a lot. But I have another problem
if I reload data(Ctrl+R) I get a blank chart

could you help me understand why? Thanks

[Taoufiq_Zarra · Accepted solution]

can you check  your Excel path

if not you can create the graph :

dimension : WEEKTC

Mesures :

CORR (this year) : =sum({1<DATA_RIF_DOC=
{"<$(=Num(Date(Makedate(Year(data_corrente), 12, 31)))) >$(=Num(Makedate(Year(data_corrente), 1, 1)))"}
,D_DIREZIONE_FLD={"PASTIFICIO"},D_PRODOTTO_FLD={"PROFESSIONALE"},C_MERCATO_STORICO_DEFSTAT={"ITALIA"}>} W_TOT_IMP_RIGA_NETNET)

CORR (last year up to the current week) : =sum({1<DATA_RIF_DOC=
{"< $(=Num(Date(Date(WeekStart(Makedate(Year(AddYears(data_corrente, -1)), Month(data_corrente), Day(data_corrente))))))) > $(=Num(Makedate(Year(AddYears(data_corrente, -1)), 1, 1)))"}
,D_DIREZIONE_FLD={"PASTIFICIO"},D_PRODOTTO_FLD={"PROFESSIONALE"},C_MERCATO_STORICO_DEFSTAT={"ITALIA"}>} W_TOT_IMP_RIGA_NETNET)

CORR (last year from to the current week) : =sum({1<DATA_RIF_DOC=
{"<$(=Num(Date(Makedate(Year(AddYears(data_corrente, -1)), 12, 31)))) >$(=Num(Date(WeekStart(Makedate(Year(AddYears(data_corrente, -1)), Month(data_corrente), Day(data_corrente))))))"}
,D_DIREZIONE_FLD={"PASTIFICIO"},D_PRODOTTO_FLD={"PROFESSIONALE"},C_MERCATO_STORICO_DEFSTAT={"ITALIA"}>} W_TOT_IMP_RIGA_NETNET)

[paolojolly]

I found the solution

Reloading document field data_rif_doc changes format from numeric 

to date DD/MM/YYYY

I modified set analysis in 

=Sum({$<DATA_RIF_DOC={">$(=Date(Makedate(Year(data_corrente), 1, 1),'DD/MM/YYYY'))<$(=Date(Makedate(Year(data_corrente), 12, 31),'DD/MM/YYYY'))"}
,D_DIREZIONE_FLD={"PASTIFICIO"},D_PRODOTTO_FLD={"PROFESSIONALE"},C_MERCATO_STORICO_DEFSTAT={"ITALIA"}>} W_TOT_IMP_RIGA_NETNET)

forcing date format

Many thanks for your help