topic Re: Set Analysis Date Range in QlikView

Set Analysis Date Range

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

I am attempting to compare a customer's quantity sold by part for the previous 6 months versus the company. I have the date range code no problem. But I'm having trouble with the set analysis to force the 6 months. I am attempting to use a variable named v6MonthStart (inappropriately named) in the set analysis.

The variable is defined as this:

'>=' & date(monthStart(addmonths(Today(),-6))) & ' <=' & date(MonthEnd(addmonths(Today(),-1)))

which produces this as desired. >=9/1/2017 <=2/28/2018 (verified via text box).

My expression labeled Company 6 Months Average is:

=Sum({$<Date={$(v6MonthStart)}>} [Invoice Qty]) / 6

I'm getting no data.

Suggestions please?

Re: Set Analysis Date Range

kaushiknsolanki — Thu, 29 Mar 2018 11:58:54 GMT

Hi,

Try this.

=Sum({$<Date={"$(v6MonthStart)"}>} [Invoice Qty]) / 6

Regards,

Kaushik Solanki

Re: Set Analysis Date Range

Chanty4u — Thu, 29 Mar 2018 12:01:22 GMT

may be

=Sum({$<Date={"=$(=$(v6MonthStart))"}>} [Invoice Qty]) / 6

Re: Set Analysis Date Range

sunny_talwar — Thu, 29 Mar 2018 12:03:48 GMT

Or one of these

=Sum({$<Date={"$(=v6MonthStart)"}>} [Invoice Qty]) / 6

=Sum({$<Date={"$(=$(v6MonthStart))"}>} [Invoice Qty]) / 6

Re: Set Analysis Date Range

sunny_talwar — Thu, 29 Mar 2018 12:06:56 GMT

Suresh -

I don't think the syntax is right with an equal sign before the dollar sign expansion

=Sum({$<Date={"=$(=$(v6MonthStart))"}>} [Invoice Qty]) / 6

Re: Set Analysis Date Range

Chanty4u — Thu, 29 Mar 2018 12:07:58 GMT

it working in my current app sunny

Re: Set Analysis Date Range

sunny_talwar — Thu, 29 Mar 2018 12:08:56 GMT

Can you share an example file where this is working?

Re: Set Analysis Date Range

Chanty4u — Thu, 29 Mar 2018 12:09:35 GMT

this one am using without any issues

count({<Flag={2},DateNum={"=$(=$(vMaxdate))"},ID={'NA'}>}Op_ID)

Re: Set Analysis Date Range

cranium144 — Thu, 29 Mar 2018 12:10:39 GMT

This one produced a number.

=Sum({$<Date={"$(=$(v6MonthStart))"}>} [Invoice Qty]) / 6

I ended up going with this to force all customers.

=Sum({$<Date={"$(=$(v6MonthStart))"}, [Bill To Company]=>} [Invoice Qty]) / 6

Re: Set Analysis Date Range

sunny_talwar — Thu, 29 Mar 2018 12:10:48 GMT

I will test out and see what I find. Thanks buddy

Re: Set Analysis Date Range

Chanty4u — Thu, 29 Mar 2018 12:15:21 GMT

only with variable the result i can show you

i used in kpi object count({<datenum=variable>})

Re: Set Analysis Date Range

sunny_talwar — Thu, 29 Mar 2018 12:19:20 GMT

Just because you see a number, it doesn't mean the expression is right...

But let me check and get back to you in a little bit

Re: Set Analysis Date Range

Chanty4u — Thu, 29 Mar 2018 12:21:15 GMT

okay sunny check from your end give me some suggestions on this

Re: Set Analysis Date Range

sunny_talwar — Thu, 29 Mar 2018 12:28:35 GMT

Here is what I am seeing in QlikView... not sure if Qlik Sense is different... but will test the same thing in Qlik Sense next

When I add the equal sign... I am seeing all rows and not just the max date row. This is true if I use direct expression or variable with or without equal sign in the variable definition.

Re: Set Analysis Date Range

sunny_talwar — Thu, 29 Mar 2018 12:33:02 GMT

Seeing similar issue in Qlik Sense....

Re: Set Analysis Date Range

manu1512 — Thu, 29 Mar 2018 12:45:15 GMT

stalwar1‌

Hi Sunny,

Can you please tell me the difference between two expression above.

Regards

Manu

Re: Set Analysis Date Range

sunny_talwar — Thu, 29 Mar 2018 12:49:26 GMT

Just depends on how the variable is declared..

If for example the variable is

'>=' & date(monthStart(addmonths(Today(),-6))) & ' <=' & date(MonthEnd(addmonths(Today(),-1)))

Then you would need double dollar sign expansion

=Sum({$<Date={"$(=$(v6MonthStart))"}>} [Invoice Qty]) / 6

If the variable is

='>=' & date(monthStart(addmonths(Today(),-6))) & ' <=' & date(MonthEnd(addmonths(Today(),-1)))

Then you would need this

=Sum({$<Date={"$(=v6MonthStart)"}>} [Invoice Qty]) / 6

I have not tested it in the above scenario, but that is what I think should work....

Re: Set Analysis Date Range

manu1512 — Thu, 29 Mar 2018 12:52:01 GMT

would be grateful to you if you can tell me the scenario where double dollar sign is used .It is asked in my interview questions.

Re: Set Analysis Date Range

Chanty4u — Thu, 29 Mar 2018 12:52:37 GMT

if variable value is with "=" in the expression "=" is not required ? could you please explain this sunny?

Re: Set Analysis Date Range

sunny_talwar — Thu, 29 Mar 2018 12:55:23 GMT

Not sure I understand your question....