topic Calander Help in QlikView

Calander Help

sona_sa — Wed, 03 Sep 2014 09:53:36 GMT

Here is one qvw attached, The problem is here :

I have 2 years data.

Actual Data :

From Jul 2013 to Mar 2014

and Budget Data -

From Jan 2013 to Dec 2014.

When I am developing the chrat, Actual value is coming correct on the selection of Year - 2014, i.e - 15.60 (Jan - 14 to Mar - 14).

But budget value is coming for all Month from Jan - 2014 to Dec - 2014. But It should to come as per our actual value till March 2014, i.e 16.46. But it is coming 68.60 - Overall year.

Also my month is not disable for Apr - to Dec.

Re: Calander Help

sona_sa — Thu, 04 Sep 2014 03:21:12 GMT

Any Update.

Re: Calander Help

jagan — Thu, 04 Sep 2014 03:57:02 GMT

Hi,

I think there is an issue in your datamodel, attach some sample data and your expected output.

Regards,

Jagan.

Re: Calander Help

rwunderlich — Thu, 04 Sep 2014 04:10:08 GMT

You don't seem to have any link between Sales (SALES_AMT_USD) and Month? So what defines what the YTD month is?

-Rob

Re: Re: Calander Help

sona_sa — Thu, 04 Sep 2014 04:21:46 GMT

Hi Jagan,

Please find the attached xls for data. Need calander where If I select Year 2014, OGC - JAN, Type - Uncertify - Then Actual (SALES_QTY) - and Budget - (AOP_Cts) should to come. Month will be there only for Jan / Feb / Mar and From May to Dec will be disabled. Because I have Actual data only till Jun - 2014. And Budget Data is for whole year 2014.

But what ever selection I have written there that selection data is till Mar - 2014 - (Actual Data)

Thanks

Re: Re: Calander Help

sona_sa — Thu, 04 Sep 2014 04:24:25 GMT

Ok Rob,

Please find the attached xls for sample data and Scenario is like this:

Please find the attached xls for data. Need calendar where If I select Year 2014, OGC - JAN, Type - Uncertify - Then Actual (SALES_QTY) - and Budget - (AOP_Cts) should to come. Month will be there only for Jan / Feb / Mar and From May to Dec will be disabled. Because I have Actual data only till Jun - 2014. And Budget Data is for whole year 2014.

But what ever selection I have written there that selection data is till Mar - 2014 - (Actual Data).

Thanks

Re: Calander Help

sona_sa — Thu, 04 Sep 2014 07:29:16 GMT

Hi Rob,

Can u make an expression for me, Scenario is like this,

My Expression =

If(Year = '2014',

((sum({$<Year = {2014},MONTH = {"<=$(vMonth)"},HalfYear,Quarter,Month>} SALES_AMT_USD/1000000)-sum({$<Year = {2014},MONTH = {"<=$(vMonth)"},HalfYear,Quarter,Month>} AOP_Value/1000000))

/sum({$<Year = {2014},MONTH = {"<=$(vMonth)"},HalfYear,Quarter,Month>} AOP_Value/1000000)*100),

((sum({$<Year={$(=max(Year))},MONTH={'<=$(=Max(MONTH))',Quarter,HalfYear, Month}>} SALES_AMT_USD/1000000)-sum({$<Year={$(=max(Year))},MONTH={'<=$(=Max(MONTH))',Quarter,HalfYear, Month}>} AOP_Value/1000000))

/sum({$<Year={$(=max(Year))},MONTH={'<=$(=Max(MONTH))',Quarter,HalfYear, Month}>} AOP_Value/1000000)*100))

Mean by using this expression I am getting -100 value, So I want to replace -100 with 0.

Thanks