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: 
Not applicable

SET ANALYSIS: SHOW TOTALS WITHOUT SHOWING A VALUE IN THE DIMENSION

cedriclupo@ all,

I am facing some problems when calculating a TOTAL expression:

The problems is focus on one Dimension "Answer", which has two values, Yes and No.

I want my expressions, to be calculating regarding the Yes Answers, but when I have to calculate an Expression called Total Answers, this has to take into account the total Answers (Yes and No) so when I put a total in my expression, the No answer in the dimension appears, and I do not want this value of the dimension to appear.

The actual expression for this total answers that I have already is the following one:

sum( TOTAL<Question, store,date, $(= GetCurrentField(TotalStores))>

aggr(count( Answer),store,date  $(= GetCurrentField(TotalStores)),Question, Answer))

Could some one advice me about how to do this total taking into accoun the "NO" Answers but in the dimension this value not to appear?

You can find an example of the output I would like to get without the No value of the dimension to appear.

Thanks in advance

1 Solution

Accepted Solutions
rubenmarin

Hi Beatriz, you can try using a different expression for totals, in example:

If (Dimensionality>0, Kaushik'sExpression, YourCurrentExpression)

So row by row it uses Kaushik's expression to show only YES values, for totals use your current expression which uses YES and NO

View solution in original post

8 Replies
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi,

Try this

sum( {<Answer = {"YES"}>}TOTAL<Question, store,date, $(= GetCurrentField(TotalStores))>

aggr(count( Answer),store,date  $(= GetCurrentField(TotalStores)),Question, Answer))

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
Not applicable
Author

Hello! Thanks for your answer,

However, if I include that restrinction, the total only shows the totals regarding the Yes responses and I want to have the totals of Yes and No.

kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi,

Can you share the qvw file with sample.

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
rubenmarin

Hi Beatriz, you can try using a different expression for totals, in example:

If (Dimensionality>0, Kaushik'sExpression, YourCurrentExpression)

So row by row it uses Kaushik's expression to show only YES values, for totals use your current expression which uses YES and NO

Not applicable
Author

Hello Ruben,

Effectively using an If has solved my problem, I wanted to know if there could be another options instead of using an "If",

rubenmarin

Hi Beatriz, come to my mind different options (using Pick(), an extra field...) but all based in a check, so it will be similar that use an 'If', if it needs to avoid 'YES' in row but count them in TOTALS this will need a way to decide when count 'YES' or not.

Maybe some other user has a better idea to do this. As a crazy idea you can add a upper level with 3 options:

-YES: related with YES

-NO: related with NO

-TOTAL: related with YES and NO

Sorted by load order. Anyway if it was me I will prefer to go with the 'If' that trying this.

Please mark Kaushik's answer at least as 'Helpful', I feel like I stole his answer.

Not applicable
Author

Thanks!! Both gave me correct aswers! But I can only select one

kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi Ruben,

You didnt steal anything.

And post should be marked as answered which gives exact answer. Because it helps others with similar problem to get exact answer.

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!