Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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)
attached new version
i replace If ... with Set Analysis
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
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)
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