topic Re: sum from different tables in QlikView

sum from different tables

Anonymous — Fri, 26 Aug 2016 13:25:03 GMT

I'm quite new to qlikview. so wanted to know about "best practice" on a sum if we are doing in our qlik view.

At the moment we are doing af below. Were you can see date an Currency "check"

<CODE>

if($(vGBPConversion)=1,

Sum ({$<CalendarYear = {$(=Max(CalendarYear))}>} [Sales LCY F4])

//same formular - but in Euro - ExRate

((Sum ({$<CalendarYear = {$(=Max(CalendarYear))}>} [Sales GBP F4]))))

</CODE>

Now we would like to ad yet a dimension. the "F4". Which is our forecast/Budget. Of these we have 5 different. BU , F1, F2, F3 and F4.

Today we have 5 object pivots for everytime we are comparing sales to budgets. We would like to remove 80% of these, by putting them into 1.

so our question are how to do this.? and preferably in a way so we also remove 80% of the calculations. to get some better performance.

as a side note every forecast are in it's own table. with the same tags. so it's also called Sales LCY F3 etc.

We would love to have an option to choose what table 1 column in the object are calculating in, WITHOUT doing is as a filter(very important) so lets say we could create a slider with the 5 table names, and selecting 1 of the options in this slider would not affect the filter, only the SUM function would the look into that table.. And we could then maybe create a second slider for comparison options?

But can this be done? And How?

we would need help with how to create the slider if this is possible. and maybe also the code.

Our thoughts on the code would be (not in real code, just to show)

Re: sum from different tables

Gysbert_Wassenaar — Fri, 26 Aug 2016 14:06:53 GMT

Create a Cyclic Group (on the Groups tab of the Document Properties window) and add your five dimensions to that group. Then use that group as dimension in your pivot table so users can choose which field in the group should be used as chart dimension.

Re: sum from different tables

Anonymous — Mon, 29 Aug 2016 06:27:52 GMT

Hi Gysbert.

Thanks for your input. But maybe I explained my self wrong.(or i'm misunderstanding what you mean) if you see the picture. I would like to compare the different forecast sales. So i would need to be able to choose different input.

With a cyclic group. I can only do it in pivot, not in charts.? And the choosing would count for the entire pivot correct? if I understand this correct, this dosn't seem usable.

again if you look a the picture, it compares F3 and F4. today this is changed manually in the expressions for each column. i have only taken a snap of the first columns. we would have several columns. one for the month, one for YTD and one for full year. So if we could have 2 sliders.

first would chose actual forecast, second would choose comparison forecast. so in the picture it could say F1 instead of F3. but a simple click in the first slider.. Is this possible?

Re: sum from different tables

Anonymous — Mon, 29 Aug 2016 08:50:59 GMT

So I think I maybe stumbled on the solution.

But not quite there.

I have created 2 list boxes with the names BU, F1, F2, F3, F4 made them alternate state.

And i have gotten the label to show the names but using =Concat({[1st forecast]}[Forecast Actualities])

Now i only need to find a way to make the table name dynamic.

Here's my try, but it dosn't work. So can someone help med to get it to sum on this [Sales LCY F1] when 1st forecast state have been chosen F1?

(sum[[Sales LCY &Concat({[1st forecast]}[Forecast Actualities])]])

Re: sum from different tables

Gysbert_Wassenaar — Mon, 29 Aug 2016 15:04:53 GMT

Try: sum($(='[Sales LCY ' & Only({[1st forecast]} [Forecast Actualities]) & ']'))

Re: sum from different tables

Anonymous — Mon, 29 Aug 2016 15:42:33 GMT

Dosn't seem to work. 😞

Why are the '[' after Sales LCY? should it be in front?

sum(Sales LCY &$(='[' & Only({[1st forecast]} [Forecast Actualities]) & ']'))

What i'm trying achive are more of less Sum([Sales LCY F1]) and since the name are with spaces the entire field name needs brackets right?

And when this work, i need to also add the calender so it ends up looking like this

(sum({$<CalendarYear = {$(=max(CalendarYear))}, CalendarMonthName = {"<=$(=max({<CalendarYear={$(=max(CalendarYear))}>} CalendarMonthName))>$(=max({<CalendarYear={$(=max(CalendarYear))}>} CalendarMonthName)-1)"}>} [Sales GBP F1]))

Re: sum from different tables

Gysbert_Wassenaar — Mon, 29 Aug 2016 17:57:19 GMT

Can you post a small qlikview document that illustrates the problem?

Re: sum from different tables

Anonymous — Wed, 31 Aug 2016 10:32:22 GMT

sorry for the late reply.. Took sometime stripping it from information. i have created an application where you have 4 qvd files being loaded. 2016 sales. 2016 BU, 2016 F1 & Forecast actualities(this is only 5 values to achieve the alternate states, where you can choose BU, F1, F2 etc.)

I have created, a table. where you can see 2016 sales. I have removed the calender options for simplicity reason.

you can see how Sales work. And how the F1 work. So if we could get the BU(last column to work, on the "new" method. that would be great. !

please note i'm only created two customers with BU number. one of them are the first customer, so it's possible to see when it works 🙂

Re: sum from different tables

Gysbert_Wassenaar — Wed, 31 Aug 2016 16:11:07 GMT

See attached example.

Re: sum from different tables

Anonymous — Thu, 01 Sep 2016 06:53:43 GMT

Thank you!! This is just perfect!!

Re: sum from different tables

Anonymous — Tue, 14 Feb 2017 12:36:08 GMT

So I have been creating Variables to put it into the label names. so i would only need to change it a few places, if i ever wanted to change anything. and to have the labels display the correct forecast. But to avoid label names being to "fuzzy" to look at.

But this got me thinking.

This formula works perfectly

sum($(='[Sales LCY ' & Only({[Primary forecast]} [Forecast Actualities]) & ']'))

but i I create a variable called PRIMARY and it contains Only({[Primary forecast]} [Forecast Actualities])

I can then create the formula

sum($(='[Sales LCY ' & PRIMARY & ']'))

This makes it so much easier to look at, and to get an overview. Would i run into any problems doing it this way instead? And would it impact perfoms if i change alot of formulas, to look at the variable instead?