topic Re: Set analysis in QlikView

Set analysis

Wed, 25 Nov 2020 16:16:04 GMT

Hi,

we are displaying 5 years of sales

2010 2011 2012 2013 2014 2015 Total

Japan 20 40 50 60 70 50

USA 30 30 40 50 60 80

Requirement:

1)when i select 2015 sales it should show only 2015 sales ant that total only other should display as zero

2)if no selections are there that should display 5 years sales

thanks

Re: Set analysis

swuehl — Sun, 20 Mar 2016 19:46:27 GMT

It should be possible to show all dimensional values even when you select a specific one by enabling

'Show all Values' on Dimensions tab of chart properties.

Re: Set analysis

Sun, 20 Mar 2016 19:51:04 GMT

Thanks for reply

ok but how to write set analysis expression eg.if i selected 2015 it showld only show 2015 .onlu current selectioniw would be help full if any sample application,thanks

Re: Set analysis

swuehl — Sun, 20 Mar 2016 20:08:03 GMT

Not sure if I understand. If you enable 'Show all Value' for year dimension and then select 2015, you should see all year dimension values (not reduced to 2015), but the expression should return the value only for 2015 and zero for all other years. I think this should fulfill your requirements:

Requirement:

1)when i select 2015 sales it should show only 2015 sales ant that total only other should display as zero

2)if no selections are there that should display 5 years sales

If it doesn't, please clarify your requirements and post some sample records or a sample QVW that demonstrate your setting.

Re: Set analysis

sunny_talwar — Sun, 20 Mar 2016 20:24:05 GMT

May be a little different approach then Stefan's way, but seems to be working

Expression: =If(Sum({1} Sales) > 0, Sum(Sales))

Make sure to uncheck 'Suppress Zero-Values' on the presentation tab.

Re: Set analysis

Sun, 20 Mar 2016 21:10:08 GMT

hi sunny,

i my case i i wrote different expression for different year it's not working in my case

PFA

Thanks

Re: Set analysis

swuehl — Sun, 20 Mar 2016 21:13:40 GMT

If you are using different expressions, you can enable the conditional expression feature on expression tab and set a conditon to enable / disable each expression maybe like

=Sum({<Year *= {2015} >} 1)

as an example for the condition for the expression for year 2015.

edit:

Or since you want to show the columns all the time, but zero the expressions for excluded years:

Don't use the conditional expression, but change your set expression from

=Sum({<Year = {2015} >} Value)

=Sum({<Year *= {2015} >} Value)

Note again the intersection operator *

Re: Set analysis

sunny_talwar — Sun, 20 Mar 2016 21:15:46 GMT

Do you have to use straight table? Pivot table is not an option? Check if the attached is helpful

Re: Set analysis

Sun, 20 Mar 2016 21:24:25 GMT

thanks.

its working fine but small doubt

1)if my user want to see last 10 months of ofsales in straight or pivote table with total.

like if he selected Mar 16 month in filter. he should see March month sales else with out selection he want to see 10 month of sales like june 2015 mar 2015 (10 months of data in 10 columns)

in this senario is it work.

sorry for asking many Questions

Re: Set analysis

sunny_talwar — Sun, 20 Mar 2016 21:29:17 GMT

I don't think your attached application have data for Mar 2016? Is there a better sample you can share?

Re: Set analysis

Sun, 20 Mar 2016 21:58:07 GMT

PFA Smple file

Re: Set analysis

swuehl — Sun, 20 Mar 2016 22:03:01 GMT

As I suggested above, use the intersection operator

sum({<MonthYear *= {'Mar-16'}>} sales)

And maybe Rangesum() for the total.

Or just a YearMonth dimension instead of multiple expressions.

edit:

And I also suggest to use

Date(Monthstart(F3),'MMM-YY') as MonthYear,

to create MonthYear values that reference the monthstart, not distinct dates in the month.

Re: Set analysis

sunny_talwar — Sun, 20 Mar 2016 22:06:42 GMT

Again I am not fully sure if we are meeting your requirement or not, but can you check if this works?

UPDATE: just a side note, try using MonthName() function to create MonthYear field or use Date(MonthStart(Date), 'MMM YYYY')

Re: Set analysis

Mon, 21 Mar 2016 03:43:48 GMT

Thanks Almost it meats my requirement.but one thing left.if a user select month it showing respective month ok.

but in my application it contain 5 years data. now i need to show current month and last 5 month of sales only.

like restrict the column like only last five months of sales.should show but i applied the above solution in my application it showing all months data.( i think we need to write different measures in expression tab)

thanks lot.

Re: Set analysis

jagan — Mon, 21 Mar 2016 04:00:25 GMT

Hi,

Check this link for rolling 6 months set analysis expression

Set Analysis for Rolling Periods

Regards,

jagan.

Re: Set analysis

sunny_talwar — Mon, 21 Mar 2016 18:30:35 GMT

See if the attached is working for you.

Re: Set analysis

Mon, 21 Mar 2016 20:06:58 GMT

Hi Sunny, that's a very helpful and a handy way to not only show all dimensions data but also to show the null.

Thanks