sum from different tables

[Anonymous]

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)

[Gysbert_Wassenaar]

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.

[Anonymous]

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?

[Anonymous]

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])]])

[Gysbert_Wassenaar]

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

[Anonymous]

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]))

[Gysbert_Wassenaar]

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

[Anonymous]

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 🙂

[Gysbert_Wassenaar · Accepted solution]

See attached example.

[Anonymous]

Thank you!!  This is just perfect!!