Use of variables in Set Analysis

[#1]

Hello,

I have a table like this

PN, SN, Date

e.g.

PN1, SN1, 10/08/2013

PN2, SN2, 10/11/2013

PN3, SN1, 05/10/2012

...

I have to present a graph (pivot table) like this:

SN, count(PN), count(PN) where Date >= vPrevious180days

e.g.

SN1, 2, 1

SN2, 1, 1

vPrevious180days is a variable calculated as follow

= Date(Num#(Num#(Num(Today (0))-180)),'DD/MM/YYYY')

e.g. Today is 16/01/2014, then vPrevious6Months gets the value 20/07/2013

I built a graph, where SN is a dimension.

Expressions are:

Count(DISTINCT(PN))

count(DISTINCT{$<[Date]  =  {"># vPrevious180days"}>} (PN))

but it does not work.

While specifying the date value (not using the variable) it works

count(DISTINCT{$<[ Date]  =  {">01/08/2013"}>} (PN))

Can someone help?

Many thanks

[#2 · Accepted solution]

Hi,

See a doc I published on Set Analysis, there is the answer you ar elooking for:

http://community.qlik.com/docs/DOC-4951

in fact, there is $(variableName)

like {<TIME_KEY = {"<$(vFirstPeriod)"}>}

Fabrice

[kiranmanoharrode]

Dear Martina

Make Variable as below

vTest  = Date(Today()-180,'DD/MM/YYYY')

and Use set Expression

=Count(Distinct{<Date={">=$(vTest)"}>} PN)

Regards

Kiran Rode

+91 8976977897

[giakoum]

maybe just a typo :

Use $ instead of # :

count(DISTINCT{$<[Date]  =  {">$(vPrevious180days)"}>} (PN))

[#5]

Dear Kiran, your answer works as well

thanks!

Martina