topic Re: Straight Table Quarter Issue in QlikView

Straight Table Quarter Issue

Sat, 13 Feb 2016 19:59:47 GMT

I have a straight table with Year as a Dimension. I am using Q1,Q2,Q3 and Q4 as quarters and generatiing the quarters with the 4 Expressions as Q1,Q2, Q3 and Q4

If(Month<='3',

Num((Sum({<Year={$(=Max(Year)-1),$(=Max(Year)-2),$(=Max(Year)-3)},MPL_Flag={'1'}>} Total_Incurred) ) ,'$#,##0'),

Num((Sum({<Month={'Mar'},Year={$(=Max(Year)-1),$(=Max(Year)-2),$(=Max(Year)-3)},MPL_Flag={'1'}>}Total_Incurred) ) ,'$#,##0'))

If (Month <='3','0',If(Month >'3' and Month <= '6',

Num((Sum({<Year={$(=Max(Year)-1),$(=Max(Year)-2),$(=Max(Year)-3)},MPL_Flag={'1'}>} Total_Incurred) ) ,'$#,##0'),

Num((Sum({<Month={'Jun'},Year={$(=Max(Year)-1),$(=Max(Year)-2),$(=Max(Year)-3)},MPL_Flag={'1'}>}Total_Incurred) ) ,'$#,##0')))

and so on

But looks like the table is always evaluating a static number using expressions, its ignoring for example

If I select Feb , Q1 still gives me

Num((Sum({<Month={'Mar'},Year={$(=Max(Year)-1),$(=Max(Year)-2),$(=Max(Year)-3)},MPL_Flag={'1'}>}Total_Incurred) ) ,'$#,##0'))

If I select Apr, Q2 still give me

Num((Sum({<Month={'Jun'},Year={$(=Max(Year)-1),$(=Max(Year)-2),$(=Max(Year)-3)},MPL_Flag={'1'}>}Total_Incurred) ) ,'$#,##0')))

What shall I do so that when I select Jan or Feb or March I shall get numbers for Jan , Feb and March in Q1 column

Re: Straight Table Quarter Issue

sfatoux72 — Sat, 13 Feb 2016 21:26:17 GMT

In your IF, you use : Month<='3' <== numeric value

And in your Set Analysis, you use : Month={'Jun'} <== string value

Are you sure that your field Month is Dual. If it's not the case, it could explain the comportement you describe.

Re: Straight Table Quarter Issue

Sat, 13 Feb 2016 21:41:21 GMT

Sample QVW

Re: Straight Table Quarter Issue

Sat, 13 Feb 2016 21:41:55 GMT

Just attached the sample QVW

Re: Straight Table Quarter Issue

swuehl — Sat, 13 Feb 2016 21:59:07 GMT

If you are only using Year as dimension, not Month, you can't have your condition check if the Month field value is smaller or larger than a number, this condition will always be evaluated to FALSE, since there is no unique Month value to compare to.

See

Use Aggregation Functions!

Re: Straight Table Quarter Issue

Sat, 13 Feb 2016 22:00:43 GMT

I did add month to the dimension but same result. Can you pleas look at the sample QVW I have attached to the original thread and let me know if you can find something

Re: Straight Table Quarter Issue

maxgro — Sat, 13 Feb 2016 22:12:25 GMT

if you want 4 expression for quarters I think you can modify your exp

If(Month <='3',

Sum({<Year={$(=Max(Year)-1),$(=Max(Year)-2),$(=Max(Year)-3)}>} Amount),

Sum({<Year={$(=Max(Year)-1),$(=Max(Year)-2),$(=Max(Year)-3)},Month={'Mar'}>} Amount))

with

Sum({<Month={'jan','feb','mar'},Year={$(=Max(Year)-1),$(=Max(Year)-2),$(=Max(Year)-3)}>} Amount)

Re: Straight Table Quarter Issue

Sat, 13 Feb 2016 22:18:08 GMT

Doest work, can u pls attach sample qvw if u have. Please keep in mind that Mar is cumulative of Jan, Feb and March, so cant do jan feb and march

Re: Straight Table Quarter Issue

sfatoux72 — Sat, 13 Feb 2016 22:39:07 GMT

Try with ‌:

if( max(month) <= 3,

...

Re: Straight Table Quarter Issue

swuehl — Sat, 13 Feb 2016 22:45:21 GMT

You need to apply a set expression also to your Month comparison in the conditon, to allow QV to evaluate the Month also for the years you haven't selected (but these are the only years you are showing):

If(Only({<Year>} Month) <= 3,

Sum({<Year={$(=Max(Year)-1),$(=Max(Year)-2),$(=Max(Year)-3)}>} Amount),

Sum(TOTAL<Year> {<Year={$(=Max(Year)-1),$(=Max(Year)-2),$(=Max(Year)-3)},Month={'Mar'}>} Amount))

If you are using a Month dimension, you would also need a TOTAL<Year> qualifier in your else branch, otherwise the projection of this aggregation (of March's Amount) to Month > 3 will be zero.

Also, you will get two lines for a Month selection <3.

But I assume you want to remove the Month dimension anyway (as I read your OP), then the result should now be correct (assuming you are using always one selected value in Month field).

Re: Straight Table Quarter Issue

swuehl — Sat, 13 Feb 2016 23:04:03 GMT

Attached a version that also works in with Month dimension.

Re: Straight Table Quarter Issue

Mon, 15 Feb 2016 05:18:46 GMT

I am sorry I thought its working , but when I was testing it realized there is a row with '0' for Q1 and Q2 I dont get that row, cuz I am supresssing 0 values, but if I un supress 0 values I get all years not just (-1,-2 and -3). Am I missing something. I would like to show the 0 values only for the years requested

Re: Straight Table Quarter Issue

jonathandienst — Mon, 15 Feb 2016 05:54:06 GMT

If you want to analyse the data by quarters, you should include quarters in your model as a derived field. Since you deleted the load script from the sample you uploaded, I can only give you guidelines, but assuming that Month is coming from your data source and that Month is a dual value:

LOAD

...

If(Month <= 3, Dual('Q1', 1),

If(Month <= 6, Dual('Q2', 2),

If(Month <= 9, Dual('Q3', 3), Dual('Q4', 4)))

) As Quarter,

...

Now you can use Month selection(s) and dimensions to view the results of selected months and Quarter selections or dimensions to view the data by quarter. Your expression becomes simply:

=Sum(Amount) // for the selected year(s)

=Sum({<Year = {"$(=Max(Year))"}>} Amount) // for the selected year

=Sum({<Year = {"$(=Max(Year)-1)"}>} Amount) // for the prior year

...

Re: Straight Table Quarter Issue

jonathandienst — Mon, 15 Feb 2016 05:56:28 GMT

If you want data for the last 3 years:

=Sum({<Year = {">=$(=Max(Year)-3) <=$(=Max(Year))"}>} Amount)

=Sum({<Year = {">=$(=Max(Year)-3) <=$(=Max(Year)-1)"}>} Amount)

depending on your exact requirements

Re: Straight Table Quarter Issue

Anonymous — Mon, 15 Feb 2016 06:06:59 GMT

load

Year(Date) as Year,

Month(Date) as Month,

'Q' &Ceil((month)/3) as quarter

use this code it useful to you