Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a Cross-Sell chart, analyzing the changes respect the previous Year of sales at product group level.
The chart show for each customer:
in the column CROSS_IN the value "IN" when the condition [Sales Previous year=0, Sales Current Year>0] is met.
in the column CROSS_OUT the value "OUT" when the condition [Sales Previous year>0, Sales Current Year=0] is met.
Please see an example in the following table.
CUST | GROUP | PREV_YEAR | CUR_YEAR | CROSS_IN | CROSS_OUT |
---|---|---|---|---|---|
Customer-01 | KIWI | 0 | 1520 | IN | |
Customer-01 | MANGO | 0 | 740 | IN | |
Customer-01 | ANANAS | 250 | 0 | OUT | |
Customer-01 | APPLE | 1890 | 0 | OUT | |
Customer-02 | KIWI | 0 | 190 | IN | |
Customer-02 | MANGO | 0 | 920 | IN | |
Customer-02 | APPLE | 640 | 0 | OUT | |
Customer-02 | ANANAS | 980 | 2030 | ||
Customer-03 | KIWI | 0 | 880 | IN | |
Customer-03 | MANGO | 280 | 490 | ||
Customer-03 | APPLE | 750 | 0 | OUT | |
Customer-03 | ANANAS | 840 | 920 | ||
Customer-04 | MANGO | 570 | 570 | ||
Customer-04 | ANANAS | 700 | 750 | ||
Customer-04 | KIWI | 740 | 1480 | ||
Customer-04 | APPLE | 850 | 0 | OUT |
Now, I would to apply a filter showing in the chart ONLY rows representing IN or OUT conditions. In other words, I would to suppress rows where [Sales Previous Year>0 AND Sales Current Year>0]
So, the Table resulting from filter should be:
CUST | GROUP | PREV_YEAR | CUR_YEAR | CROSS_IN | CROSS_OUT |
---|---|---|---|---|---|
Customer-01 | KIWI | 0 | 1520 | IN | |
Customer-01 | MANGO | 0 | 740 | IN | |
Customer-01 | ANANAS | 250 | 0 | OUT | |
Customer-01 | APPLE | 1890 | 0 | OUT | |
Customer-02 | KIWI | 0 | 190 | IN | |
Customer-02 | MANGO | 0 | 920 | IN | |
Customer-02 | APPLE | 640 | 0 | OUT | |
Customer-03 | KIWI | 0 | 880 | IN | |
Customer-03 | APPLE | 750 | 0 | OUT | |
Customer-04 | APPLE | 850 | 0 | OUT |
Any idea on how to apply this filter?
I attach a qvw document containing an example.
Thanks for help!
Emilio
Check this
It may be best to create these fields as flags in your script - use the same conditions and create a resident load to your table that aggregates the sales by the customer, group and year,.
You could nest it to create one flag based on 2014 and 2015 data or create two separate flags for each year. That way you can either use a listbox to literally filter the data, or you can use it within your set analysis.
Check this
Thank you Mr. Cheliyan, wonderful!