Set Analyse: YTD and Fix "product"

[#1]

Hello,

I have two questions

1)

I made two bar chart e.g. one with the sum of the sold products per Quarter and the other with the sum of that Year.

By changing the selection I get the right results but I don't want that by the second bar chart (Year).

The second bar chart (Year) has to be the same when the month of quarter change (Year To Date). 

When I select an other product then the second bar chart must change (the sum of sold products is different).

Q: what is the expression?

2)

Almost the same as question 1.

The chart representing only one product.

For example: the products are bike, car and motor. I only want to see the sum of sold product bike for the quarter (bar chart 1) en the Year (bar chart 2).

By changing the selection month or quarter the second bar (Year) chart must not change.

I tried: Sum(if(Type='bike',sold)) that works to see only the sum of sold for the product bike. But I don't get the YTD in the expression

See attacht file.

Thanks

Ralph

[tmumaw]

When you are loading your date table do something like this. 

FiscalCalendar:

LOAD FISPD,
            FISYR,
            [Billing Date] as Date,
            WEDAT,
            num(WEDAT, '####0') as WEDAT_Num,
            WKNO,
            if([Billing Date] <= '$(vToday)' AND FISPD <= '$(vFisPd)' AND WKNO <= '$(vWkNo)', 1) AS YTDFlag,
            if([Billing Date] <= '$(vToday)' AND FISPD = '$(vFisPd)' AND WKNO <= '$(vWkNo)', 1)   AS MTDFlag,
            if([Billing Date] <= '$(vToday)' AND FISPD = '$(vFisPd)' AND WKNO = '$(vWkNo)', 1)    AS WTDFlag,
            if([Billing Date] <= '$(vPrevToday)' AND [Billing Date] >= '$(vRolling5)', 1)                         AS RTD5Flag,
            if([Billing Date] <= '$(vPrevToday)' AND [Billing Date] >= '$(vRolling4)', 1)                         AS RTD4Flag,
            if([Billing Date] <= '$(vToday)' AND [Billing Date] >= '$(vRolling3)', 1)                                 AS RTD3Flag

FROM (qvd)

   where FISYR >= '$(vPrevYear)' and FISYR < '$(vYear)';

You will have to set some variables, but this works great for YTD, MTD, WTD, RTD (Rolling to Date).  Then here is the expression to use.

sum({<FISYR = {$(vFisYr)}, YTDFlag = {1}>} [Inv Gross])                             You would use this one for current YTD. 

sum({<FISYR = {$(=$(vFisYr) - 1)}, YTDFlag = {1}>} [Inv Gross])                Then something like this for prior YTD. 

Hope this helps.