Set Analysis Date Range

[cranium144]

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?

[Chanty4u]

only with variable the result i can show you

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

[sunny_talwar]

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

[Chanty4u]

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

[sunny_talwar]

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.

[sunny_talwar]

Seeing similar issue in Qlik Sense....

[manu1512]

stalwar1‌

Hi Sunny,

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

Regards

Manu

[sunny_talwar]

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....

[manu1512]

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.

[Chanty4u]

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

[sunny_talwar]

Not sure I understand your question....