CODEWITHSUNDEEP
qlikview
VivekDev
VivekDev

Reputation: 25349

Qlikview set analysis in chart expression based on dimension

I have a chart showing Issued Qty for all transactions from 'From Location' to 'To Location' as follows.

enter image description here

Now I want to show one more column. In it I want to show transaction qty from 'To Location' to 'From Location'. To elaborate, the first row shows Qty issed from Pharmacy to 3rd Flr - NEW AC WARD. So now in the next coloumn, I wish to shwo the qtys issued from 3rd Flr - NEW AC WARD to pharmacy.

Some trick using set analysis would do, but I am not well versed with Set analysis. Please help.

I tried something like this. But this is not working. :(

=Sum ({$<[From_Location_Name] = {[To_Location_Name]}, [To_Location_Name] = {[From_Location_Name]}>}[MatlMoveIssuedQty])

I also tried the following, but did not work

=Sum ({$<[From_Location_Name] = p(To_Location_Name)>}[MatlMoveIssuedQty])

Upvotes: 1

Views: 3755

Answers (1)

The Budac
The Budac

Reputation: 1633

I think what you're trying to do isn't possible because you can't tell QlikView to honour the dimensionality (keep everything on the FROM line) and ignore it at the same time (set analysis TO=FROM). QV has a lot of intelligence built into it's handling of dimensions and the associativity of the data behind those dimensions.

I do have 2 possibilities for you to display your data though.

Simplest method would be a pivot table with FROM down the left and TO across the top. could potentially get messy if your dimensions have lots of values in

Pivot Table example

Second way would be to create an orphan table of the Locations so that users can select them without breaking the underlying associativity. that involves a few more steps.

first in the script, the important part is the second part, creating a distinct list of the combinations of FROM and TO and naming them something that does not associate to anything anywhere else in the data.

MEDS:
load * inline [
FROM,   TO, QTY
Pharm,  3rd,    1
Pharm,  1st,    2
Pharm,  2nd,    3
2nd,    Pharm,  45
3rd,    Pharm,  6
1st,    Pharm,  76
3rd,    Pharm,  53
];

LOCS:
load distinct 
FROM    as  LOC1,
TO  as  LOC2
resident MEDS;

Then you can build these 2 objects next to each other to show your TO and FROM figures.

FROM and TO with a single Selection

Notice that when nothing is selected the 2 objects equivalent but with different sorting. Depending on what you're trying to achieve a calculation condition might be in order to guide users to analyse one location at a time.

Clear all

Upvotes: 2

Related Questions