Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Tricky Data Range Question

Hi Everyone,

I am working on a pivot table that will calculate a value, compare that value to a range, and based on where the calculated value falls in the range, display a final value on the report.  I'm going to try to explain this using an example:

I have 4 ranges, one for each 'Type':

Type A Range:
MinRangeMaxRangeLimit
025001
250150002
500175003
7501100004

Type B Range:
MinRangeMaxRangeLimit
050001
5001100002

Type C Range
MinRangeMaxRangeLimit
030001
300160002
600190003
9001120004

Combined Range
MinRangeMaxRangeLimit
020001
200140002
400160003
600180004
8001100005
10001120006

The final report should look like this:

CenterTypeMonth1Month2Total1Total2LimitRemaining
1A000500022
1 B000500011
1C101500021
1Total101500032
2A011350021
2B011350010
2C000350022
2Total022350020
3A000900044
3B000900022
3C202900031
3Total202900053

Where:

- 'Total1' is a total of the values in 'Month1' and 'Month2' for each 'Type'

- 'Total2' is a total of production, completely separate from all previous information and is calculated by summing the aggregate of production by Center only, using NoDistinct

- 'Limit' is to be determined by locating where 'Total2' falls in the ranges above for each type, and using the combined range for the total line.

- 'Remaining' is 'Limit' - 'Total1'

Right now, I have a lookup table for the ranges, keyed off of 'Type' and with a Sequence Number to identify each line. I have tried several things, and I believe I have gotten the closest with the following:

If(Count({$<"$vTotalProduction" = {">$(=VMSMinRange) <$(=VMSMaxRange)"}>} Distinct VMSSeqNo) = 1, VMSLimit)


In the above set analysis, vTotalProduction = the same equation used to calculated 'Total2'.  However, because I haven't figured out how to isolate the 'Type' in this set analysis, I have to make a selection in Type to narrow it down, and this still counts all of the range values for that type rather than isolating the one record where vTotalProduction is within the range.

Has anyone ever encountered something like this?  Any help would be greatly appreciated!

1 Solution

Accepted Solutions
MVP
MVP

Re: Tricky Data Range Question

Elizabeth

I think he attached example does what you want, except for the combined limits - I have not figured out a way to do them - but this is a start...

Regards

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
5 Replies
MVP
MVP

Tricky Data Range Question

Hi Elizabeth

I would consider using interval matching - one per range type - on Total2. Then I think the problem becomes a lot simpler.

TypeA:

LOAD * Inline

[

     AFrom, ATo, AValue

     0, 2500, 1

     2501,5000, 2

...

];

IntervalMatch(Test)

LOAD * Resident TypeA;

Now just get the value of AValue, BValue, CValue for Limit.

Hope that helps

Jonathan

Regards

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable

Tricky Data Range Question

Hi Jonathan,

I believe I know how to do what you're suggesting, and I'm going to try and see if I can get this to work.  However, if possible I would like to avoid having to determine Total2 in the load script.  We have a large dataset already, so I'm trying to do what I can to avoid growing the size of the file.  Thank you for your help!

Not applicable

Tricky Data Range Question

Alright,

I have tried using interval match in the load script, but I'm afraid it is going to be too cumbersome.  The problem I am running into is that I will need to calculate every possible Total2 amount that could occur from the user's date selection, and then use that in the interval match.  I really need to find away where Total2 can be compared to the range when the selection is made, to avoid loading a lot of extra data into the script.  Are there any other ideas out there?  Thank you for your help!!

MVP
MVP

Re: Tricky Data Range Question

Elizabeth

I think he attached example does what you want, except for the combined limits - I have not figured out a way to do them - but this is a start...

Regards

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable

Re: Tricky Data Range Question

Thank you, Jonathan, this was a huge help!  I just had to change a few things to fit my actual data model, but this pulled exactly what I needed for the detail information.  I was not able to get this to work for the total, either, so what I did was add "total" records to my actual data and treated them like normal detail data.  This means I wasn't able to completely avoid adding additional records to the data model, but the size increase was miniscule, especially compared to the other tries I had attempted.

Thank you again for working on this!!

Community Browser