sequence of if conditions affect results

[#1]

Hi All,

I have multiple if conditions in my expr.

Dimension as Month 1,2,3, Q1, 4,5,6, Q2, 7,8,9, Q3, 10,11,12, Q4, Year.

So I write in expr as

(If Month = Q1, expr1,

(If Month = Q2, expr2,

(If Month = Q3, expr3,

(If Month = Q4, expr4,

If Month =1, expr5,

If Month =2, expr6,

If Month =3, expr7,

If Month =4, expr8,

similarly for other months and year.

But i get  0 value against Q4.

So now if I change the order from Q1, Q2, Q3, Q4 to Q4, Q1, Q2, Q3 then I get value in Q4, but 0 in Q3.

I would like to know how does the sequencing will affect the expr?

[jonathandienst]

Hi

Is there a particular reason you are folding the quarter and month into a single dimension and is there a reason that you have a different expression for each month/quarter?

It seems to me that there may be a simpler way to solve your problem. Otherwise I suggest that you post a sample app with the expressions and sample data.

Regards

Jonathan

[#3]

Hi Jonathan,

I will explain you how exactly I want it.

I have Year and Month for selection.

If I select Month 3, it should show value in column for M2 =1, M2=2, M2=3 and for Qtr data till selected Month(3).

I want to show pivot table with columns as month and also qtr in between and at the last year column.

and on the expression side I have Actuals, budget, variance and Variance %.

So I created 1 more field for Month as dimension and called it as M2 and added it as dimension.

and dragged it on the top of the expression to create sub columns

and then on the expression,

I checked if M2 = 1, expr1 and similarly....

[#4]

Hi Jonathan,

Did you understand the issue? or I confused you?

[vijay_iitkgp]

Hi Apurv,

Why dont you use Pivoting of Year,  Qtr and Month instead of creating seperate dimension.

[jonathandienst]

Hi

I agree with Vijay. Create quarter and month dimensions and pivot as he has shown in his example.

Regards

Jonathan

[#7]

Hi Vijay and Jonathan,

I did try your solution.

I created quarter and Month as a dimension, but doing partial sum on the quarter level, I am getting 0 for partial sum.

Also I would like to tell, On the expression column of actuals,

I have used many variables to calculate GP, GP%,  NP%, Cash Profit etc, so I do have those if conditions also.

Please let me know how to fix this issue.

[jonathandienst]

Hi

The problem lies somewhere in the structure of your data or the calendar. Please post your qvw (or a sample showing your problem) so that we can look at it further.

Regards

Jonathan

[#9]

Hello Jonathan,

Please check the attached qvw file and the excel file for the data.

[jonathandienst]

Hi

Cant help right now, but looking quickly at your model, you are ignoring the advice already given above (perhaps not intentionally), and I cant help wondering what it is you are trying to achieve. The "data source" is a set of financial statements with a mixture of values, %'s and ratios and I am not sure how you expect to aggregate this data across quarters, let alone across years. Normally we would read the GL accounts and create the financial statements - perhaps this not possible in your case.

Anyway, I am not sure that I can help you much with that data source, it needs more supporting data to be able to work with in any reliable or useful fashion. I think you should consider hiring a consultant to understand your requirements and to give you professional advice.

Regards

Jonathan