Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
hello
i am a beginner with qlikview. i have a table in my data-warehouse in which i have negative and positive amounts in a columns
something like this:
id | Type | amount sold |
---|---|---|
1 | sales | 200 |
2 | return | -150 |
3 | sales | 200 |
i want just the positive amounts would be seen in just one of my pivot tables. so i dont want to use a list box and ask the end user to filter on Type=sales
is there any where in pivot table definition window to restrict data shown in it?
thank you
Mana
Hi Mana - let me know if this solves your issue -
QlikView has a very power expression syntax known as Set Analysis - it can doing many calculations with the in-memory data model - amazing things.
For your example - Set Analysis allows you to perform test conditions on your data to get exactly what you are looking for. So for example.
Sum(AmountSold) -- aggregates amount sold
Sum ({ <AmountSold ={">=0"}>}AmountSold)
States - aggregate amount sold where value of amountsold is greater than or equal to 0
You add this expression - to the expression box
I attached the QVW - but with the free personal edition you are limited to the number of QVWs you can open from others - (if you do not have a license)
Let me know
Mike
Hi Mana - This is the Sense forum - I will move your post over to the QlikView forum and also attempt to get you support for your issue.
Mike
if I have understood correctly, here's my proposed solution
Hi Mana - let me know if this solves your issue -
QlikView has a very power expression syntax known as Set Analysis - it can doing many calculations with the in-memory data model - amazing things.
For your example - Set Analysis allows you to perform test conditions on your data to get exactly what you are looking for. So for example.
Sum(AmountSold) -- aggregates amount sold
Sum ({ <AmountSold ={">=0"}>}AmountSold)
States - aggregate amount sold where value of amountsold is greater than or equal to 0
You add this expression - to the expression box
I attached the QVW - but with the free personal edition you are limited to the number of QVWs you can open from others - (if you do not have a license)
Let me know
Mike
i have got the point.
thank you friends.
Mana
Hi
In your chart properties under expressions tab, use the below
RangeMax(Sum([amount sold]),0)
or
Sum({$<Type={"=aggr(Sum([amount sold]), Type) > 0 "}>}[amount sold])