Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor
Contributor

Set Analysis using Greater than with Distinct Values

So here is the long and short of it.

I have a table with a list of assets (AssetName) and A Chart that I would like to display the count of Distinct Assets by month (regardless of selections.

Count({$<Month=>} Distinct AssetName))

No Problem - Works flawlessly.

Now I would like to count a subset of that population based on the comparison of two other fields for each distinct AssetName

Field One = CycleTime

Field Two = DesignTime

I tried something like Count({$<Month=>,CycleTime={">DesignTime"}>} Distinct AssetName)), but nothing.  I tried a few variations using Aggr, to no avail. 


Can Anyone Help?

12 Replies
Highlighted
Specialist
Specialist

Re: Set Analysis using Greater than with Distinct Values

Hi Robert. If DesignTime has the only value try this

Count({$<Month=>,CycleTime={">$(=DesignTime)"}>} Distinct AssetName))

and check the DateTime format of these fields, it should be the same

another case something like this

Count({$<Month=>,CycleTime={">$(=Date(Max(DesignTime)))"}>} Distinct AssetName))


Highlighted

Re: Set Analysis using Greater than with Distinct Values

You can also try this

Count({$<Month, AssetName = {"=CycleTime > DesignTime"}>} DISTINCT AssetName)

Highlighted
Specialist
Specialist

Re: Set Analysis using Greater than with Distinct Values

Hi Sunny, I think it is perfect in some cases. I have to try it somewhere)

Highlighted

Re: Set Analysis using Greater than with Distinct Values

I think this will work only if AssetName can only have a single unique value of CycleTime and DesignTime. If this is not true, then we would need a sample to determine what exactly is needed.

Highlighted
Contributor
Contributor

Re: Set Analysis using Greater than with Distinct Values

The following is a sample table of data for the Set Analysis in question:

    

AssetNameShiftDateMonthCycle TimeDesignTime
Asset.One8/2/2018Aug49.642.7
Asset.One8/3/2018Aug56.742.7
Asset.One8/4/2018Aug55.142.7
Asset.One9/1/2018Sep56.842.7
Asset.One9/2/2018Sep53.542.7
Asset.One9/3/2018Sep66.642.7
Asset.Two8/2/2018Aug53.242.7
Asset.Two8/3/2018Aug58.242.7
Asset.Two8/4/2018Aug50.542.7
Asset.Two8/5/2018Aug41.542.7
Asset.Two9/1/2018Sep52.742.7
Asset.Two9/2/2018Sep51.042.7
Asset.Two9/3/2018Sep62.342.7
Asset.Two9/4/2018Sep47.242.7
Asset.Three8/2/2018Aug24.742.7
Asset.Three8/3/2018Aug24.642.7
Asset.Three8/4/2018Aug24.542.7
Asset.Three8/5/2018Aug24.842.7
Asset.Three9/1/2018Sep24.542.7
Asset.Three9/2/2018Sep24.742.7
Asset.Three9/3/2018Sep24.642.7
Asset.Three9/4/2018Sep24.642.7
Asset.Four8/2/2018Aug38.242.7
Asset.Four8/3/2018Aug37.742.7
Asset.Four8/4/2018Aug39.742.7
Asset.Four8/5/2018Aug38.242.7
Asset.Four9/1/2018Sep39.642.7
Asset.Four9/2/2018Sep39.742.7
Asset.Four9/3/2018Sep39.042.7
Asset.Four9/4/2018Sep39.042.7
Asset.Five8/2/2018Aug34.442.7
Asset.Five8/3/2018Aug40.642.7
Asset.Five8/4/2018Aug33.242.7
Asset.Five8/5/2018Aug48.442.7
Asset.Five9/1/2018Sep35.842.7
Asset.Five9/2/2018Sep36.242.7
Asset.Five9/3/2018Sep35.442.7
Asset.Five9/4/2018Sep36.842.7
Asset.Six8/2/2018Aug38.542.7
Asset.Six8/3/2018Aug38.542.7
Asset.Six8/4/2018Aug38.742.7
Asset.Six8/5/2018Aug38.342.7
Asset.Six9/1/2018Sep39.142.7
Asset.Six9/2/2018Sep39.442.7
Asset.Six9/3/2018Sep39.242.7
Asset.Six9/4/2018Sep39.042.7
Asset.Seven8/2/2018Aug26.542.7
Asset.Seven8/3/2018Aug26.342.7
Asset.Seven8/4/2018Aug26.442.7
Asset.Seven8/5/2018Aug26.542.7
Asset.Seven9/1/2018Sep26.442.7
Asset.Seven9/2/2018Sep26.642.7
Asset.Seven9/3/2018Sep26.542.7
Asset.Seven9/4/2018Sep26.442.7
Asset.Eight8/2/2018Aug41.042.7
Asset.Eight8/3/2018Aug40.542.7
Asset.Eight8/4/2018Aug42.342.7
Asset.Eight8/5/2018Aug41.342.7
Asset.Eight9/1/2018Sep42.042.7
Asset.Eight9/2/2018Sep42.242.7
Asset.Eight9/3/2018Sep41.742.7
Asset.Eight9/4/2018Sep41.942.7
Asset.Nine8/2/2018Aug36.542.7
Asset.Nine8/3/2018Aug38.642.7
Asset.Nine8/4/2018Aug30.242.7
Asset.Nine8/5/2018Aug43.042.7
Asset.Nine9/1/2018Sep35.142.7
Asset.Nine9/2/2018Sep33.842.7
Asset.Nine9/3/2018Sep29.342.7
Asset.Nine9/4/2018Sep35.442.7
Asset.Ten8/2/2018Aug39.442.7
Asset.Ten8/3/2018Aug39.142.7
Asset.Ten8/4/2018Aug40.142.7
Asset.Ten8/5/2018Aug39.142.7
Asset.Ten9/1/2018Sep40.242.7
Asset.Ten9/2/2018Sep40.342.7
Asset.Ten9/3/2018Sep40.242.7
Asset.Ten9/4/2018Sep39.842.7

Ultimately I would like to count how many assets each month area Cycling Slower (Cycle Time) than the Design Cycle Time (Design Time)

Highlighted

Re: Set Analysis using Greater than with Distinct Values

May be try this?

Count(TOTAL <Month> {<AssetName = {"=[Cycle Time] > [DesignTime]"}>} DISTINCT AssetName)

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
Contributor III
Contributor III

Re: Set Analysis using Greater than with Distinct Values

Hello Robert,

Use following set expression (not sure correct 100% but it is working perfectly).

Count({<[Cycle Time]={"=[Cycle Time] > DesignTime"}>} distinct AssetName)


Untitled.png

Highlighted
Creator II
Creator II

Re: Set Analysis using Greater than with Distinct Values

Hi Robert,

As I understand in your table you have following fields:


AssetName
CycleTime
DesignTime


If so, you can create Flag in the script load:

Load
     AssetName,
     CycleTime,
     DesignTime,
     If(CycleTime > DesignTime, 1, 0) AS Flag
From ...

After this you will be able to use Flag in the Set Analysis:

Count({$<Month =, Flag = {1}>} DISTINCT AssetName)

From my point of view this solution will have better performance and of course in this case it's easier to write set expression.

Thanks,
Vitalii

Highlighted

Re: Set Analysis using Greater than with Distinct Values

Asset.Two was slower on 8/5/2018, but was faster on the other days of August. Do you want this to be counted or not?