topic Ignoring a specific selection in a sum in QlikView

Ignoring a specific selection in a sum

Mon, 23 Nov 2009 22:21:43 GMT

Good day

I have been having some fun with Qlikview for the last 3 months, but it seems I have reached a stumbling block with regards to aggregation.

I have the following scenario. There are 3 sales reps. These reps are assigned budget codes (or areas). I also have a fact table will all transactions relating to the reps and budget codes.

This is my problem. When I select a rep, I need to see the total sales for all budget codes linked to the rep, but the total must include all the other reps sales as well. Currently, Qlikview is displaying all the linked budget codes for the selected rep, but then it only shows the sales for the selected rep (instead of giving a total sum of all transactions for the selected budget codes).

My question is thus, is it possible to do a sum for all transactions for the selected budget codes (which are linked to the rep), but ignore the actual rep code selection?

Ignoring a specific selection in a sum

Mon, 23 Nov 2009 22:47:14 GMT

Yes, it's doable, but it can get a little tricky. First, are the users making the actual selection on the rep or the budget code? I'm assuming they select a rep, leaving you with a list of possible budget codes. You can get that list of codes using Concat:

Concat(FIELD, ',')

Then you need to use the 1 Set Identifier to ignore selections (this will get you everything):

Sum({1} Sales)

Then to combine them, something like:

Sum({1 <BudgetCode = {"($=Concat(FIELD, ','))"}>} Sales)

Depending on the type of your Budget Codes, that may or may not work. If you need quotes around each of your budget codes to work, switch the Concat part to be:

($=Concat(FIELD, '","'))

When you put that all together, you may have issues with the quotes. It will depend on your data.

Ignoring a specific selection in a sum

Tue, 24 Nov 2009 14:50:17 GMT

Thank you very much for your help. I had to do a little bit of a work around seeing as when I display the BudgetCode totals, that I still need to display each budget code separately in a grid. But this definitely steered me in the right direction