topic Show data for current week in QlikView

Show data for current week

stekol61 — Wed, 25 Nov 2020 16:16:04 GMT

Hi!

I have an app where i want to show the number of Tickets for the current week no matter what other date filters that are used in the app.

E.g. the date filter could be set for the previous 3 months and the result is presented in a graph but I also want to present the value for the current week

Re: Show data for current week

bramkn — Tue, 26 Feb 2019 15:36:03 GMT

Use set analysis and exclude all the date fields.
something like {<date,day,year,week={thisweek}>}

Re: Show data for current week

stekol61 — Tue, 26 Feb 2019 15:40:52 GMT

But i want that all graphs should show data based on the dynamic filers. Only one specific text box should always show data for the current week.
Will the set analysis solve this?

Re: Show data for current week

Shubham_Deshmukh — Wed, 27 Feb 2019 07:07:46 GMT

YUP possible, try this :
=Sum({<filterToAvoid1=,filterToAvoid2=,filterToAvoid3=>}If(date>=WeekStart(today()) and date<=WeekEnd(today()),field))
Let me know this works for you or not.

Re: Show data for current week

bramkn — Wed, 27 Feb 2019 07:05:23 GMT

Set analysis will put standard filters on a measure. if you make sure to include the calander values without an actual value. This to exclude these selections. You will make it always show the current week. when you set it to it. There are a lot of tutorials on how it works. It might be usefull to read up on it as it might help you solve some of your other wishes.

Re: Show data for current week

bramkn — Wed, 27 Feb 2019 07:10:42 GMT

@Shubham_Deshmukh wrote:
YUP possible, try this :
=Sum({<filtersToBeAvoid1=,filtersToBeAvoid2=,filtersToBeAvoid3=>}If(date>=WeekStart(today()) and date<=WeekEnd(today()),field))
Let me know this works for you or not.

Dont use an if for this.

use this: Sum({<filtersToBeAvoid1=,filtersToBeAvoid2=,filtersToBeAvoid3=,date={">=$(=WeekStart(today())) <=$(=WeekEnd(today()))"}>}field))

There might be some brackets misplaced so make sure to check in qlik.

Re: Show data for current week

Shubham_Deshmukh — Wed, 27 Feb 2019 07:30:59 GMT

Hi @bramkn ,

Ya, agreed.

=Sum({<filterToAvoid=,date ={">=$(=weekStart(today()))<=$(=weekEnd(today()))"}>} sale)

Re: Show data for current week

stekol61 — Wed, 27 Feb 2019 08:56:41 GMT

Hi!

Not sure about the syntax to use.

I have a filter 'Period' that can be either 'Week' or 'Month' (See below).

I tried to use '<filterToAvoid=,Period ' but it doesn't show the correct result

Re: Show data for current week

Shubham_Deshmukh — Wed, 27 Feb 2019 10:42:27 GMT

Can you share your expression?

Re: Show data for current week

stekol61 — Wed, 27 Feb 2019 11:00:24 GMT

Hi!

Here it is:

=Count({<filterToAvoid=,Period ={">=$(=weekStart(today()))<=$(=weekEnd(today()))"}>} wor_ticket_ID)

It does not override the 'Period' selection

(I'm using Sense, don't know if that matters)

Re: Show data for current week

Shubham_Deshmukh — Wed, 27 Feb 2019 11:29:21 GMT

Stekol61,

**Note : filterToAvoid = use fieldName which you want to avoid, fieldToAvoid is not keyword.
And that solution is according to dates not according to week or month.
So it could be like,
=Count({<filterToAvoid=,yourDateFields={">=$(=weekStart(today()))<=$(=weekEnd(today()))"}>} wor_ticket_ID)

**yourDateFields = from which you are getting month /week.

Also if you want to show current week only then you don't need any filter value just go with today(), it will give you current week.

Re: Show data for current week

stekol61 — Wed, 27 Feb 2019 11:58:24 GMT

I tried this:

=Count({<filterToAvoid=,Period={">=$(=weekStart(today()))<=$(=weekEnd(today()))"}>} wor_ticket_ID)

It does not override the 'Period' filter (e.g selecting week '2019-6' with 'Period' filter shows the result for that week , not the current week).

I also tried your suggestion "don't need any filter value just go with today()" but I get no result. Is the syntax correct?

=Count(Year(Today()) &'-'& Week(Today()) wor_ticket_ID)

Re: Show data for current week

Shubham_Deshmukh — Wed, 27 Feb 2019 12:04:06 GMT

It is showing you result for selected week because you are using filterToAvoid word, I am saying it should be field. As you wanna avoid Period,
=Count({<Period=,yourDateField={">=$(=weekStart(today()))<=$(=weekEnd(today()))"}>} wor_ticket_ID)
**yourDateField = should be date field, not Period

=Count(Year(Today()) &'-'& Week(Today()) wor_ticket_ID) - not at all, what you are trying to do with this

Re: Show data for current week

stekol61 — Wed, 27 Feb 2019 12:39:23 GMT

This gives no result. The expression used for 'Period' is a variable '$(vDim'
=Count({<Period=,[$(vDim)]={">=$(=weekStart(today()))<=$(=weekEnd(today()))"}>} wor_ticket_ID)

I tried to follow you second recommendation ("don't need any filter value just go with today()" ) and created this.
=Count(Year(Today()) &'-'& Week(Today()) wor_ticket_ID)
('Today()) &'-'& Week(Today()' gives the correct week format, e.g. '2019-9')

Re: Show data for current week

Shubham_Deshmukh — Wed, 27 Feb 2019 12:55:27 GMT

I think, miscommunication is going on here. Please state your exact requirement with date field

Try : sum (wor_ticket_ID), not count(wor_ticket_ID)

Re: Show data for current week

stekol61 — Wed, 27 Feb 2019 13:10:19 GMT

OK. Hope this clarify things:
What I need is to present number of tickets 'count(wor_ticket_ID)' for the current week no matter what time period is selected. The result can be presented in e.g. a text box.
The select time period should work for all other graphs in the app

Re: Show data for current week

Shubham_Deshmukh — Wed, 27 Feb 2019 13:16:36 GMT

Great, our discussion was on right track then,
=Count({<Period=,[$(vDim)]={">=$(=weekStart(today()))<=$(=weekEnd(today()))"}>} wor_ticket_ID)
Now above exp will avoid Period selection and give you the result.
this will work, but what is in variable [$(vDim)]?

Re: Show data for current week

stekol61 — Wed, 27 Feb 2019 13:44:29 GMT

Your expression returns '0'.

$(vDim) is created like this:
Dims:
LOAD
Dim,
RowNo() as DimSort
INLINE [
Dim
Week,
Month
];

FACT:
LOAD
Year(wor_event_start_date)&'-'& Week (wor_event_start_date)as Week,
Year(wor_event_start_date)&'-'& Month (wor_event_start_date)as Month,

Also a variable is created:
vDim=minstring({<DimSort={$(vMinDimSort)}>}Dim)

Re: Show data for current week

Shubham_Deshmukh — Wed, 27 Feb 2019 13:59:57 GMT

I was asking for this date wor_event_start_date, try this now

=Count({<Period=,wor_event_start_date={">=$(=weekStart(today()))<=$(=weekEnd(today()))"}>} wor_ticket_ID)

Re: Show data for current week

stekol61 — Wed, 27 Feb 2019 14:09:10 GMT

It still follows the the time Period selection.
Maybe also the filer for 'Week' or 'Month' must be included. 'Dim' from the Dims table