Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

madnanansari
New Contributor III

Show totals based on selected dimension

I am working on Retail Sales Analysis.

We have a Sales target let say 100 million (total) for the year. This is stored in a table named 'Target Overall' having attributes Year, Target.

This target is divided into all stores we have except some new ones. So within 100 million 95mllion is distributed for the stores. 5 million is not yet allocated. This is stored in a table named 'Stores Target' having attributes Year, Store Id, Target.

We have target specifically for some categories as well. This is defined overall for each category. Let say perfumes have a target of 20 million. Toys have a target of 5 million whereas edible items have a target of 8 million. Some of the target is still no yet allocated. This is stored in a table named 'Category Target' having attributes Year, Category ID, Target.

Now the analysis is required to be designed in such a way that when we look at the overall Sales figures, it should compare it with the total target.

When we drill down to stores (or look at store sales) , it should compare the sales with store targets.

Similarly Category sales is required to be compared with category targets. But when we look at Sales for all categories; it should compare it with the total target of 100 million.

How this can be done?

2 Replies

Re: Show totals based on selected dimension

Concatenate all you targets into one table and add a field with the level of the target, i.e. overall, store, category etc. It would be useful to use the same names as the field names of the dimensions like store and category. You can then create a chart with a drill down group with field names like store and category. You can then use the getcurrentfield function to retrieve the name of the current level of the drilldown group. That value can be used to select the right values from the targets table: sum({<Level={'$(=GetCurrentField([MyDrilldownGroup]))'}>}Target)


talk is cheap, supply exceeds demand
madnanansari
New Contributor III

Re: Show totals based on selected dimension

It is good idea to do concatenating all the targets in one. There is one issue:

I have 2 target tables here:

first one is

Store Day Target:

-Date

-Store id

-Target

second one is

Category Month target:

-Month

-Category id

-Store

-Target

If the store is able to reach its daily target; its OK. But in the long run like a Month's time, it has to achieve the targets on the categories as well.

Community Browser