Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
basildur
New Contributor III

Using p() in set analysis when having Multiple Tables and 2 Month Fields

Hi Guys,

I am a little stuck here and hope you can help me out with a set analysis issue.

I would like to incorporate Budget and Profit figures in one table in the front end of the app using set analysis.


My Data model is as below:

dmins.PNG

What I want to do is to have a table like this, however as you can see the Budget figures give me a sum for the whole year in every month line:

dmins 2.PNG

In my set analysis I have the following and technically it should work, but it doesn't:

Sum({$< [Budget Year] = p(Year), [Budget Month] = p(Month)>}[Budget])


Any advice or ideas would be very much appreciated

1 Solution

Accepted Solutions
Partner
Partner

Re: Using p() in set analysis when having Multiple Tables and 2 Month Fields

you should use your master calendar also for your budget date.

ex.

Budget:

LOAD

...

[Budget Date] as Date

From ...

Insurance:

LOAD

...

Office as [Office Budget]

From ...

I hope it helps.

8 Replies
Partner
Partner

Re: Using p() in set analysis when having Multiple Tables and 2 Month Fields

did you check the data types for both month fields

basildur
New Contributor III

Re: Using p() in set analysis when having Multiple Tables and 2 Month Fields

Of course I did, it is in the exact same format. It was the first thing I did.

Partner
Partner

Re: Using p() in set analysis when having Multiple Tables and 2 Month Fields

what does Sum(Budget) return in this table?

basildur
New Contributor III

Re: Using p() in set analysis when having Multiple Tables and 2 Month Fields

The same, Total for the full year for every Month. I think the problem might lie in the Data Model and they way the Tables are interconnected.

Partner
Partner

Re: Using p() in set analysis when having Multiple Tables and 2 Month Fields

Can you join the budget table into the insurance table, and then use a synthetic key, comprising of office and date?

Khan_Mohammed
Honored Contributor II

Re: Using p() in set analysis when having Multiple Tables and 2 Month Fields

Maybe use

Sum({$< [Budget Year] = p([Budget Year]), [Budget Month] = p([Budget Month])>}[Budget])


p(Year) or p(Month) would take all the possible stuff


Since your calendar month, year and budget month, year are different you should use everything from budget table

Unless you map your Date to calendar

Budget:

trim(date([Budget Date],'YYYYMMDD')) as [DateID],

..

..

... From....


Master Calendar:

trim(date(Date,'YYYYMMDD')) as [DateID],

...

...

From...


This will link your budget directly to Calendar and then your Office would also be mapped to Insurance.

Guess that should work as well

Partner
Partner

Re: Using p() in set analysis when having Multiple Tables and 2 Month Fields

you should use your master calendar also for your budget date.

ex.

Budget:

LOAD

...

[Budget Date] as Date

From ...

Insurance:

LOAD

...

Office as [Office Budget]

From ...

I hope it helps.

basildur
New Contributor III

Re: Using p() in set analysis when having Multiple Tables and 2 Month Fields

Thanks Everyone, I will have a look on Monday and let you know if these suggestions worked. Much appreciated