5 Replies Latest reply: Oct 22, 2012 7:32 AM by Gysbert Wassenaar

# Cout for prev days related to a selected date

Hello,

i have this issue:

- i have a calandar to select month and day and i want to make some items count for ther previous 7 day of that date selected (i want to count items registered in the last 7 days from the selected date) per agent. for agent selection i have a list.

my model is:

item no.     reg_date     agent_id

Thank you,

Mihai

• ###### Re: Cout for prev days related to a selected date

HI

Try like this

=count({<reg_date = {'>=\$(=max(reg_date-7))<=\$(=min(reg_date))'}>}itemNo)

hope it helps

• ###### Re: Cout for prev days related to a selected date

Create a variable vDate to calculate the date from your selections of year,month and day. If your calendar fields are named calYear, calMonth and calDay this would be done like:

vDate = makedate(calYear,calMonth,calDay)

Then use the variable vDate in an expression to calculate the count of item no's in the previous 7 days:

=count({\$<[reg_date]={'>=\$(vDate-7)<\$(vDate)'  }  >} [item no.])

• ###### Re: Cout for prev days related to a selected date

Ook...but i don't uderstand your formula.

Can you explain it to me please?

Thanks!

• ###### Re: Cout for prev days related to a selected date

hi, It is set Analysis. Explore community for it you, you can found a lot of information about it.

Here I am attaching a file for your study.

Regards

- Anil

• ###### Re: Cout for prev days related to a selected date

You want to count item no.'s from the 7 days previous to a date you choose. Lets call the date you choose vDate

So the reg_date should be smaller than vDate and larger than vDate - 7 days. The set expression means just that. We start with count([item no.]). Then we add the set analysis expression to filter reg_date: {\$<[reg_date]={'>=\$(vDate-7)<\$(vDate)'}>}.

The entire set analysis expression is enclosed by {}. The first \$ means we use the current selections.  Between the first < and the last > are the filters, in this case only on the field [reg_date].

So [reg_date] = {..filter conditions..}. In this case the filter condition has two parts: >=\$(vDate-7) and <\$(vDate).

vDate still needs to be calculated and that's what dollar expansion does: \$(vDate) calculates the expression we used in the variable. It calculates makedate(calYear,calMonth,calDay) so we get a date back.

See this document for an introduction to set analysis:

http://community.qlik.com/servlet/JiveServlet/previewBody/1867-102-1-1858/QlikLearn-Set-Analysis-Presentation.pdf