Qlik Community

Qlik Sense Cloud Discussions

vinimpedroso
New Contributor

Gaugue with Reference Lines

Hi

I need help in Qlik Sense

Iwant to make a Gaugue with my sum(sales) with 2 selections: date(by week) and place

Not a problem so far

Now I need to make 2 Reference Lines

n1. - sum(sales) from the week before the selection week and place

n2 - sum(sales) from last month same week of the selection and place

Exemple:

I selected sum(sales) of the 3º week of FEB on Brasil in the Gaugue

so "n1" will show sum(sales) of 2º week of FEB

and "n2" will show sum(sales) of 3º week of JAN


Please need help to make Reference lines n1 and n2

1 Solution

Accepted Solutions

Re: Gaugue with Reference Lines

My extreme bad... I forgot to add double quotes

=Sum({1 < DATA = {"$(='>=' & Date(WeekStart(Max([DATA]), -1), 'DD/MM/YYYY') & '<' & Date(WeekStart(Max([DATA])), 'DD/MM/YYYY'))"}, [NOME DA CR] = {"LOCAWEB"}>} [VENDAS R$])

Getting 95.55k for last week

21 Replies
Employee
Employee

Re: Gaugue with Reference Lines

Hello Vinicius,

I don't have the exact formul, perhaps stalwar1 could chime in here - but for started it looks like you could take advantage of using the Calendar Measures feature and your existing date fields.

Check out this video:

Creating Master calendar - Qlik Sense - YouTube

It creates a Set Analysis expression that you could use in the Ref Line.

For example - I created a gauge that shows Last Year Sales and the REF line shows Sales for current year. I know this isn't the exact example -but it gives you an idea on how you can achieve this.

When applicable please mark the appropriate replies as CORRECT. This will help community members and Qlik Employees know which discussions have already been addressed and have a possible known solution. Please mark threads as HELPFUL if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads as HELPFUL if you feel additional info is useful to others.

Regards,

Michael Tarallo (@mtarallo) | Twitter

Qlik

Regards,
Mike Tarallo
Qlik

Re: Gaugue with Reference Lines

Sure mto‌....

vinimpedroso‌ do you have a date field in your application? I usually like using Date over using Month and Year field because those alone can cause problem at the turn of an year. MonthYear field can also work if that has been created using Date(MonthStart()) function. I don't usually like MonthName function because it tends to cause issue within set analysis.

Also, it would be helpful to mention the format of the date or MonthYear field you are going to share.

Best,

Sunny

vinimpedroso
New Contributor

Re: Gaugue with Reference Lines

Thanks for the anwser Michael

but still don't solf the problem, because I don't want teh current week or year, I want a selectable week

I have 2 fields on the dashboard, 1 for date(per week) e another with the stores. And I have the Gauge charts to measures sum(sales)

I will select a specific store and a week, and the chart will give the sum(sales) of that week on that store

But I need 2 references with this data, the sum(sales) for the week before that and the same week from last mounth

I hope you understend, if not I will try to copy some printscreen

Thanks

vinimpedroso
New Contributor

Re: Gaugue with Reference Lines

This is what I have:

Image 2.jpg

Week selected: 3ª of Feb (W07)

Store selected: "LOCAWEB"

Gauge Chart show me: =Sum([SALES])

Reference lines:

Last week: (green) =Sum({1<[DATA.autoCalendar.Week]={W06},[STORE]={"LOCAWEB"}>}[SALES])

Same Week Last Month: (red) =Sum({1<[DATA.autoCalendar.Week]={W03},[STORE]={"LOCAWEB"}>}[SALES])

Now it's working because I select a especific week on the reference lines ignoring my selections.

But I need the reference lines to be automatic according to my selection on date and store

Hope it's clear now

Thanks

Re: Gaugue with Reference Lines

Is this in response to my question? If it is, then I still don't think you have answered my question . If its in general, then I hope you are going to respond to my question soon

vinimpedroso
New Contributor

Re: Gaugue with Reference Lines

Yes, I have a Date field, it's only 1 field that contain day / month / year

Re: Gaugue with Reference Lines

What is its format?

vinimpedroso
New Contributor

Re: Gaugue with Reference Lines

dd/mm/yyyy, I usually use the autocalendar

Re: Gaugue with Reference Lines

May be these

n1 =Sum({1<DateField = {$(='>=' & Date(WeekStart(Max(Date), -1), 'DD/MM/YYYY') & '<' & Date(WeekStart(Max(Date)), 'DD/MM/YYYY'))}, [STORE] = {"LOCAWEB"}>} [SALES])

n2 =Sum({1<DateField = {$(='>=' & Date(WeekStart(AddMonths(Max(Date), -1)), 'DD/MM/YYYY') & '<' & Date(WeekStart(AddMonths(Max(Date), -1), 1), 'DD/MM/YYYY'))}, [STORE] = {"LOCAWEB"}>} [SALES])

These expression might be fine tuned, but they should bring you closer to your requirement

Community Browser