Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

compare weekly sales using different colors

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

Immagine.png
can someone help me?

Thanks!

1 Solution

Accepted Solutions
Highlighted
Specialist II
Specialist II

Re: compare weekly sales using different colors

can you check  your Excel path

Capture.PNG

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)

 

Regards,
Taoufiq ZARRA

View solution in original post

4 Replies
Highlighted
Specialist II
Specialist II

Re: compare weekly sales using different colors

attached new version

i replace If ... with Set Analysis

Capture.PNG

 

Regards,
Taoufiq ZARRA
Tags (1)
Highlighted
Creator
Creator

Re: compare weekly sales using different colors

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

blank char.png

could you help me understand why? Thanks

 

Highlighted
Specialist II
Specialist II

Re: compare weekly sales using different colors

can you check  your Excel path

Capture.PNG

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)

 

Regards,
Taoufiq ZARRA

View solution in original post

Highlighted
Creator
Creator

Re: compare weekly sales using different colors

I found the solution

Reloading document field data_rif_doc changes format from numeric 

date before.png

to date DD/MM/YYYY

date after.png

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