topic Re: conditional Set analysis in QlikView

conditional Set analysis

markgraham123 — Wed, 05 Aug 2015 14:14:28 GMT

Hi Everyone,

I'm fetching count(Distinct(Awards) if name='John'. count({$<Name={'John'}>} DISTINCT Awards)

I wanna restrict the year to 2015.

Can someone throw some limelight.

sinanozdemir — Wed, 05 Aug 2015 14:15:34 GMT

Try something like this:

count({$<Name={'John'}, Year = {2015}>} DISTINCT Awards)

Kushal_Chawda — Wed, 05 Aug 2015 14:16:25 GMT

try,

count({$<Name={'John'}, Year ={"$(=max(Year))"}>} DISTINCT Awards)

markgraham123 — Wed, 05 Aug 2015 14:36:08 GMT

Thanq Sinan.

markgraham123 — Wed, 05 Aug 2015 14:36:22 GMT

Thanq Kush.

both of the solutions worked

markgraham123 — Wed, 05 Aug 2015 17:41:36 GMT

Hi,

I wanna calculate the below expression using set analysis for year 2015 like

(Sum([Units])/Count(Distinct([route Number])))

If (Year=2015, (Sum([Units])/Count(Distinct([routeNumber]))))

Can you help me in the syntax pls.

Kushal_Chawda — Wed, 05 Aug 2015 17:45:00 GMT

Try this

Sum({<Year ={"$(=max(Year))"}>}[Units])/Count(Distinct{<Year ={"$(=max(Year))"}>}[routeNumber])

markgraham123 — Wed, 05 Aug 2015 17:52:32 GMT

Thanq Kush.

sasiparupudi1 — Wed, 05 Aug 2015 18:00:12 GMT

Sum({<Year={2015}>}[Units])/Count({<Year={2015}>}Distinct([route Number]))

Kushal_Chawda — Wed, 05 Aug 2015 18:03:36 GMT

Welcome Mark