Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

QV: limit Pivot table to three columns + show total values for second dimension

Dear all,

I am trying to produce the attached excel tables in QV and facing two problems while doing so:

Firstly, I must limit the Column dimension "Year" to the three most recent years where there is content in the database. Right now the dimension "year" is programmed as follows:

=if(match(Year,'2013','2014','2015','2016','2017'), Year)


If for a specific selection there is no data for 2014 and 2016, the outcome is exactly as needed, however I am looking for a way to have only 2015, 2016 and 2017 showing if there is data for all five years. I am not sure whether this can be solved within the options of the pivot table (unlikely in my opinion) or whether this must / can be solved with a specific formula in the "Year" dimension.

Secondly, the Excel table contains a total for the first dimension (column B) that is split up into all entries in the second dimension (column C). I am only finding a way to yield totals for the first dimension and/or the second dimension but not like in the excel table the results for each Country (second dimension) across all Programme types (first dimension).

Duration New.PNG

Thanks,

Benedikt

12 Replies
karthiksrqv
Partner - Creator II
Partner - Creator II

Hi,

You can do this via set analysis. The current method of using IF-MATCH is not maintainable (when an additional year's data comes up in your dataset).

Keep your dimension as just Year. If your expression is sum(sales) , instead, use this:

=sum({<Year={">=$(=max(Year,3))"}>} sales)

For the 2nd requirement,

in the Properties > Presentation , select only the first dimension, and check the 'Show Partial Sums' option.

Hoping these help!

Anonymous
Not applicable
Author

Hi Karthik,

thanks for your swift reply, Regarding the first problem, your solution seems to work really fine.

Regarding the 2nd requirement, I had already tried this, however, the only result I get from activating the partial sums for dimension 1 is this:

Partial sums1.PNG

However I would need a total for Dimension 1 split up for each of the entries in dimension 2.

Kind regards,

Benedikt

karthiksrqv
Partner - Creator II
Partner - Creator II

Hi,

How about selecting 'Show Partial Sums' for Dimension2 also?

Anonymous
Not applicable
Author

Hi,

then the result looks like the table I have posted in the original question. I really think that the name "partial  sum" does not yield partial sums for the respective dimension but simply a total.

karthiksrqv
Partner - Creator II
Partner - Creator II

Hi,

I'm not able to understand what the required output is, can you post a sample mockup/screenshot?

Anonymous
Not applicable
Author

Hi,

The desired output can be seen in the Excel File I attached to my initial post. Moreover please see a screenshot here below:

Output.PNG

Meaning I am looking to have a total, summing up all options for Dimension 1 but still splitting up for dimension 2.

KR and thanks very much for your persistence

Benedikt

Anonymous
Not applicable
Author

Hi Karthik,

I am also coming back to your solution on the three columns: The formula you suggested has the problem that if there is data only for one Year, the Pivot table does not show anything.

Do you happen to know a way to circumvent this error?

Thanks again,

Benedikt

Anonymous
Not applicable
Author

Dear Karthik, dear All,

Sorry to insist on this, but I am really struggling to find a solution to the remaining problem here:

Using this expression for my table

=sum({<Year={">=$(=max(Year,3))"}>} sales)

the table correctly filters to the three most recent years of data, however if with a certain data filter there is only data for two years or one year, the table does not show any data.

Does anybody have a possible variation of the expression to circumvent this problem?

Thanks a thousand times

Benedikt

vishsaggi
Champion III
Champion III

I vaguely remember sunny has done something like this before. You can ask him, if he can pinch in some time to work on this. Hey  stalwar1‌ any help here. ?