topic Re: Target for different years in QlikView

Target for different years

bobbydave — Wed, 25 Nov 2020 16:16:04 GMT

I have 3 files

Metric (dimension)
Budget (dimension)
Metric Values (expression)

The common key across all is Metric. I have a calendar that sorts each file out by year and month YYYYMM (filename is YYYYMM_Metric.xlsx and YYYYMM_Metric_Values.xlsx

Howevver, Budget is a yearly file so it is named YYYY_Budget.xlsx

I've attached an output I am getting. When I load the files, everything appears fine if for one year but when my Target changes each year, it is going to show all target years in my output. This is showing 2015 and 2016 Budget data

This should look like below for 2016 data

and 2015 Budget data is

I know its got to do with my YYYY grouping.

Anyone any idea how to get the Year so that it matches my Calendar

Output of what I currently have attached.

Thanks

Re: Target for different years

Digvijay_Singh — Thu, 09 Jun 2016 09:52:49 GMT

Can you pl share the final output sample you want and the sample of 3 files so that we can run the script and detect the problem. Thanks.

Re: Target for different years

Anil_Babu_Samineni — Thu, 09 Jun 2016 10:20:13 GMT

What kind of script is this.

Please try to upload qvw along with required o/p

Re: Target for different years

bobbydave — Thu, 09 Jun 2016 11:10:36 GMT

I have attached a sample app.

If you look at the Budget dimension, this is my issue.

It should look like this

Grouping Metric CostCenter Budget 2016-01 2016-02

IT OTHER CIO 6150 330604 21K 27K

It looks as if Budget is also bringing in 2015 Budget also.

Hope this is a better explanation.

Re: Target for different years

Anil_Babu_Samineni — Thu, 09 Jun 2016 11:18:17 GMT

Bobby,

You want like below...

Re: Target for different years

bobbydave — Thu, 09 Jun 2016 11:26:12 GMT

Grouping Metric CostCenter Budget 2016-01 2016-02

IT OTHER CIO 6150 330604 21K 27K

Sec & Disaster 6157 7041 ....... .......

E-business 6156 -72 ...... .....

It Controllership 6180 221 ........................................

The Second figure in Budget CIO is - 74812 Its like as if that is the figure for 2015.

There should only be those 4 metrics for 2016

If you select 2015, you will see that there are only 3 metrics and E-Business is not there but the Budget still has 2 values in the cells. There should only be one budget figure for each metric

Re: Target for different years

pho3nix90 — Thu, 09 Jun 2016 11:42:33 GMT

try this one,

I cannot reload to test if I made typing mistakes, but it will give you an idea on what you need to do. Problem is that you are not linking your targets to a date, in this case to a year. So when you are selecting a year in your selections it has no idea which bugets belong to said year, only to which "metric" since that is the only key. IN the example metric and year is concatenated to form a "target link" so that the qvw knows to which metric the budget belongs to and as well to which year.

Re: Target for different years

bobbydave — Thu, 09 Jun 2016 13:35:32 GMT

I am now getting this issue?

Re: Target for different years

pho3nix90 — Thu, 09 Jun 2016 13:46:00 GMT

Try this one

Re: Target for different years

bobbydave — Thu, 09 Jun 2016 13:59:25 GMT

Much closer but looks like Budget isn't recognising the date or metrics.

Attached is what it now looks like loaded

Re: Target for different years

pho3nix90 — Thu, 09 Jun 2016 14:04:58 GMT

Try again with this one.