topic Re: how to: set analysis or if statement in QlikView

how to: set analysis or if statement

josephinetedesc — Mon, 06 Jul 2015 04:33:55 GMT

Locality	North	North	South	South	East	East	West	West	SE	SE	TOTAL	Total
Month-Yr	Jun 2015	Jun 2014	Jun 2015	Jun 2014	Jun 2015	Jun 2014	Jun 2015	Jun 2014	Jun 2015	Jun 2014	Jun 2015	Jun 2014
Cars sold	68	62	73	72	209	191	128	155	60	56	538	536
Cars Paid	61	70	60	62	204	183	133	156	67	43	525	514
if(location = 'North',(sum(Cars sold))
or
if(location = 'South',(sum(Cars sold))
or
if(location = 'East',(sum(Cars sold))
or
sum({$}(Cars sold))
)))))

I have a pivot table and Locality is a dimenstion -so how do I get the total in this case? Even using the set analysis - i do not have a column for the Total - so i need to load a 'dummy' Locality?

Thank you

Re: how to: set analysis or if statement

jagan — Mon, 06 Jul 2015 04:40:52 GMT

Hi,

You can try Show Partial Sums option in the Presentation tab to display the total. Select Partial Sum option for all the dimensions and check.

Regards,

Jagan.

Re: how to: set analysis or if statement

josephinetedesc — Mon, 06 Jul 2015 04:47:52 GMT

Hi Jagan

is that in Presentation tab? I am using a pivot table - i cannot see Select partial sums in version 11???

Re: how to: set analysis or if statement

Mon, 06 Jul 2015 04:47:58 GMT

Hi Josephine,

In what format are you getting your input data?

Could you please elaborate?

Regards,

Jemimah

Re: how to: set analysis or if statement

jagan — Mon, 06 Jul 2015 04:50:39 GMT

Hi,

In presentation tab of Pivot table properties Show Partial Sums option is available. I think QV9 it is available, can you attach a sample file or screenshot of that tab.

Regards,

Jagan.

Re: how to: set analysis or if statement

josephinetedesc — Mon, 06 Jul 2015 04:51:23 GMT

Hi Jemimah

my input data comes from a database and an excel sheet. The database has daily values, the spreadsheet has the Localities

Re: how to: set analysis or if statement

josephinetedesc — Mon, 06 Jul 2015 04:56:52 GMT

Re: how to: set analysis or if statement

qlikviewwizard — Mon, 06 Jul 2015 04:58:45 GMT

Hi josephinetedesco,

You have to set the data as shown in attached file.

Later use the below script and create Pivot table. No need to use set analysis.

Hope it will help you.

Data:

load * ,Date([Month-Yr]) as Date;

LOAD Locality,

[Month-Yr],

[Cars sold],

[Cars Paid]

FROM

if_in_set_analysis.xlsx

(ooxml, embedded labels, table is Data);

Re: how to: set analysis or if statement

PrashantSangle — Mon, 06 Jul 2015 05:04:20 GMT

Hi,

Show Partial Sum enable after selecting Dimension.

Regards,

Re: how to: set analysis or if statement

jagan — Mon, 06 Jul 2015 05:07:00 GMT

Hi,

Click on the Dimension and select the Show Partial Sum option. If you attach the sample then it would be easier to provide the solution.

Regards,

Jagan.

Re: how to: set analysis or if statement

Mon, 06 Jul 2015 05:15:01 GMT

Hi,

You need to select dimension and then select partial sum

Re: how to: set analysis or if statement

josephinetedesc — Mon, 06 Jul 2015 05:35:06 GMT

Hi Jemimah but ... I want Cars Sold and Cars paid on the LHS and Localilty and Month on the top. Are you using a pivot table?

Re: how to: set analysis or if statement

PrashantSangle — Mon, 06 Jul 2015 05:37:17 GMT

Hi,

Drag your dimension to top.

You will get desired output.

Regards

Re: how to: set analysis or if statement

josephinetedesc — Mon, 06 Jul 2015 05:46:23 GMT

uh huh!!!! so the problem is that QlikView is not reading my expression as a number I think .... cos certainly using the qvw supplied it works, when I try my figures in QlikView ... it does not show "show partial sums" - Will epxeriment!

Re: how to: set analysis or if statement

josephinetedesc — Mon, 06 Jul 2015 05:48:50 GMT

the other thing is that in the example above 'Cars sold" is a dimension not a calculated dimension

Re: how to: set analysis or if statement

josephinetedesc — Mon, 06 Jul 2015 05:50:08 GMT

so maybe the data is more like this:

Activity	value	Date	Locality
Cars sold	68	1/05/2015	North
Cars Paid	61	2/05/2015	North
Cars sold	73	3/05/2015	South
Cars Paid	60	4/05/2015	South
Cars sold	209	5/05/2015	East
Cars Paid	204	6/05/2015	East
Cars sold	128	7/05/2015	West
Cars Paid	133	8/05/2015	West
Cars sold	60	9/05/2015	SE
Cars Paid	67	10/05/2015	SE

Wonder if I need to change my data model!

Re: how to: set analysis or if statement

josephinetedesc — Mon, 06 Jul 2015 11:40:28 GMT

How do I upload the app? In any case I have discovered the Aggr function. However still not able to get the total of all Localities on the horizontal ... ie the Total of East, North, South, West

Re: how to: set analysis or if statement

josephinetedesc — Mon, 06 Jul 2015 13:16:19 GMT

Row and Column totals in pivot table

//community.qlik.com/message/716385#716385

I cannot open the QlikView document tonight, but i will try this tomorrow morning. The discussion was:

In a pivot table, I would like to show some dimensions totals in rows and some in columns.

I have searched this community and one advice is to use Subtotals on Top/Bottom. but this is making no difference; all totals appear at the bottom or top of columns, but never at the end of rows.

This was unanswered and posted in February

Re: how to: set analysis or if statement

qlikviewwizard — Tue, 07 Jul 2015 01:22:04 GMT

Hi josephinetedesco,

Did you resolve your issue?

Re: how to: set analysis or if statement

josephinetedesc — Tue, 07 Jul 2015 01:37:54 GMT

Hi Wizard

no - not yet, although I did download the app from

//community.qlik.com/message/716385#716385

and it is what I wanted.

For the moment I have a chart with a dimension called locality and monthname and then next to it I have a cloned chart with the locality removed. This off course gave me another problem ... how do you "FIX" charts and tables so that they do not move - however it will have to do for the moment.