topic Re: Aggregate lowest cost for types over dates in QlikView

Aggregate lowest cost for types over dates

jeremy_fourman — Tue, 07 May 2013 02:59:38 GMT

Hi all, thanks for any help offered. I have hit a wall and can't figure out what I need to do, I have tried many different things but haven't progressed.

I wish to present by date the sum of the lowest contract cost for excluded ContractTypes.

The following returns me a single lowest contract cost but not the sum by ContractType.

sum(

aggr(

FirstSortedValue(

{$<

ContractType=e(ContractType)

,Usage_Flag={2}

ContractCost,ContractCost*RowNoCtr)

,Date)

)

The expression needs to only look at usage flag 2 records in the excluded contract types set.

If possible I also need to be able to identify within the usage flag 2 set the following:

If Primary is the ContractType then Primary must also be the Alternate Contract Type.

If Ancillary is the ContractType then Ancillary must also be the Alternate Contract Type.

If OffContract is the ContractType then Primary or Ancillary for the Alternate Contract Type.

----------------------------

The attached example is a very simple model, modified from an application I originally got from this site. The real application will be over month year and include some variation of this solution. If someone could take a peek at the attached document and help me out that would be amazing! Again thanks for any help I can get.

Re: Aggregate lowest cost for types over dates

jeremy_fourman — Tue, 07 May 2013 12:45:01 GMT

anyone, Bueller? Bueller?

Re: Aggregate lowest cost for types over dates

calvindk — Tue, 07 May 2013 13:07:44 GMT

it should look like this:

Sum(

Aggr( FirstSortedValue(

{$<

ContractType=e(ContractType)

,Usage_Flag={2}

ContractCost,ContractCost*RowNoCtr)

,Date, ContractType)

)

however i think there is a bug in the aggr function when including the e function. It works when you do a sum of rows, but the aggr cannot aggregate by nonselected types.

IE what it should be capable of (and is capable of in the straight chart) is this:

Sum(

Aggr( FirstSortedValue(

{$<

ContractType=e(ContractType)

,Usage_Flag={2}

ContractCost,ContractCost*RowNoCtr)

,Date, e(ContractType))

)

Edited for clarity:

I know these 2 do not work, however i partially consider this a bug in the aggr function as was mainly pointing this out.

Re: Aggregate lowest cost for types over dates

calvindk — Tue, 07 May 2013 13:10:27 GMT

This however works instead:

Sum({1}

Aggr( FirstSortedValue(

{$<

ContractType=e(ContractType)

,Usage_Flag={2}

ContractCost,ContractCost*RowNoCtr)

,Date, ContractType)

)

Re: Aggregate lowest cost for types over dates

jeremy_fourman — Tue, 07 May 2013 13:15:04 GMT

HI Anders,

Thanks for looking.

Indeed I have tried both ways that you suggested before posting.

The first way you suggest, returns zero and no dimension:

Sum(Aggr( FirstSortedValue( {$< ContractType=e(ContractType) ,Usage_Flag={2} >} ContractCost,ContractCost*RowNoCtr) ,Date, ContractType) )

The second way returns null and no dimension:

sum(

aggr(

FirstSortedValue(

{$<

ContractType=e(ContractType)

,Usage_Flag={2}

ContractCost,ContractCost*RowNoCtr)

,ContractType,e(ContractType))

)

I had thought the second way would work too when I tried yesterday. Is there any way to accomplish what I need to do within a set expression?

Thanks again.

Re: Aggregate lowest cost for types over dates

jeremy_fourman — Tue, 07 May 2013 13:16:20 GMT

I just saw your other post Anders, let me give it a try.

Re: Aggregate lowest cost for types over dates

calvindk — Tue, 07 May 2013 13:17:01 GMT

I know those 2 do not work, as aggr wont work over excluded fields, but the {1} solution should work.

Re: Aggregate lowest cost for types over dates

jeremy_fourman — Tue, 07 May 2013 13:30:28 GMT

It does Anders, marking your post as the answer, thanks for your time it is appreciated! How does the usage of the set identifier for ignoring user selection help though? Kinda scratching my head still. I love that it works, so don't get me wrong

I can open another post for the other requirements I have for this expression, but are there any ideas on how I can extend this expression to include the following requirement? I have not begun to investigate these additional requirements in all honesty since I was focused on the first piece.

-------------------------

If possible I also need to be able to identify within the usage flag 2 set the following:

If Primary is the ContractType then Primary must also be the Alternate Contract Type.

If Ancillary is the ContractType then Ancillary must also be the Alternate Contract Type.

If OffContract is the ContractType then Primary or Ancillary for the Alternate Contract Type.

-------------------------

Thanks again!

Re: Aggregate lowest cost for types over dates

calvindk — Tue, 07 May 2013 13:45:43 GMT

There are multiple ways to go about it. Depending a bit on how you are going to use it (and often on how you like to script/program) you can select from the choices.

1: You could create nested if's in your expressions

2: You could handle it in set analysis in your expression

3: You could flag your data to simplify the expressions needed and then do 1 or 2

4: You could limit your data to only have the allowed combinations

Personally i think i would do 3, ie create a flag in the data as 'AcceptedRestrictions' or something similar and then run all your restrictions into that one flag.

Best wishes

Re: Aggregate lowest cost for types over dates

calvindk — Tue, 07 May 2013 13:52:27 GMT

In regards to ignoring user selections, you can go about that in 2 ways, either specify {1} and then include what you want, or not specify {1} and then set whatever you want to ignore as blank in set analysis, ie

Sum({<Date = >} Value) // ignore selection in date

Sum({1 < Product = 'Shoes' >} Value) // ignore all selections and force something

You could try this instead for your expression, you might prefer it:

Sum({< ContractType = >}

Aggr( FirstSortedValue(

{$<

ContractType=e(ContractType)

,Usage_Flag={2}

ContractCost,ContractCost*RowNoCtr)

,Date, ContractType)

)

Re: Aggregate lowest cost for types over dates

jeremy_fourman — Tue, 07 May 2013 16:25:08 GMT

Thanks Anders, I understand the differences between them and use selection modifiers field= all the time to clear selections. I was more questioning why using {1} allows the aggr function to work with the e() element function.

Either way, it works and I greatly appreciate your help!