Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I was wondering if you could kindly help me, hopefully with the syntax in my dimensions and expressions
How Do I supress Median, Fractile and Longest Wait under the "Left without being seen" section of my attached pivot table?
Basically how do I suppress the Nulls
I only want to display "Total Attends" in the "Left without being seen" section
Kind Regards
Helen
Hi.
There is plenty of ways to do it. I used the easiest to implement, just to show the idea.
Add this load to your script (a table structure and sort order):
LOAD * INLINE [
Level1, Level2, Expression,
Total Treatment Time, Admitted, Median
Total Treatment Time, Admitted, Fractile
Total Treatment Time, Admitted, Longest Wait
Total Treatment Time, Non Admitted, Median
Total Treatment Time, Non Admitted, Fractile
Total Treatment Time, Non Admitted, Longest Wait
To Initial Treatment, , Median
To Initial Treatment, , Fractile
To Initial Treatment, , Longest Wait
Left Without Being Seen, , Total Attends
];
Then create a pivot table whith dimensions: Level1, Level2, Expression, [Reportable Month].
Add this expression (I just trunsformed yours):
=Pick(Match(Expression, 'Median', 'Fractile', 'Longest Wait', 'Total Attends')+1, 'NA', Time(Median([Total Wait])/1440, 'hh:mm'), Time(Fractile([Total Wait],0.95)/1440, 'hh:mm'), Time(Max([Total Wait])/1440, 'hh:mm'), Count([Total Wait]))
There is a match() part that calculates the appropriate index by 'Expression' dimension and pick() part that picks the cooresponding expression.
As there is only one expression, the text formats should be handled explicitly, thats why I added Time() function.
Hi.
I think, you can't do it that way.
I suggest to transform your column with expressions so thet it will be a dimension.
Create your table structure instead of using ValueList().
Then use just appropriate complex expression.
Hello
I know this is a big ask, but I have been trying to do this in my script, but resorted to ValueLists instead
With the example I have provided, are you able to provide me with more assistance on this?
Even if a little bit of help is all you can do
Thanks
Helen
Hi.
There is plenty of ways to do it. I used the easiest to implement, just to show the idea.
Add this load to your script (a table structure and sort order):
LOAD * INLINE [
Level1, Level2, Expression,
Total Treatment Time, Admitted, Median
Total Treatment Time, Admitted, Fractile
Total Treatment Time, Admitted, Longest Wait
Total Treatment Time, Non Admitted, Median
Total Treatment Time, Non Admitted, Fractile
Total Treatment Time, Non Admitted, Longest Wait
To Initial Treatment, , Median
To Initial Treatment, , Fractile
To Initial Treatment, , Longest Wait
Left Without Being Seen, , Total Attends
];
Then create a pivot table whith dimensions: Level1, Level2, Expression, [Reportable Month].
Add this expression (I just trunsformed yours):
=Pick(Match(Expression, 'Median', 'Fractile', 'Longest Wait', 'Total Attends')+1, 'NA', Time(Median([Total Wait])/1440, 'hh:mm'), Time(Fractile([Total Wait],0.95)/1440, 'hh:mm'), Time(Max([Total Wait])/1440, 'hh:mm'), Count([Total Wait]))
There is a match() part that calculates the appropriate index by 'Expression' dimension and pick() part that picks the cooresponding expression.
As there is only one expression, the text formats should be handled explicitly, thats why I added Time() function.
Wow!
Thank you so much.....I would of been here years, months and days and would not of achieved that at this stage in my Qlikview devleopment
Could I push my luck and ask whether you can help me with the final stage
-I have entered the inline statement
-Created a pivot table as you have suggested, but I am not sure where to pick the [Reportable Month] from for my dimension?
-Also, my pivot table only comes out with 1 line of data when I copy the expression in
Level1 | Expression | Level2 | Data |
---|---|---|---|
Left Without Being Seen | Total Attends | 6 |
You have been really helpful
Thanks
Helen
1) [Reportable Month] << its your field, just add it as dimension to the end.
2) Change the order Expresion<->Level2
Thank you! This is just what I need to achieve