topic Re: Problem in Expression in QlikView

Problem in Expression

Mon, 18 Jun 2012 02:45:38 GMT

Hi ,

I have two columns in the dataset : Date(Start) and Date(End). And I have to print them as Date(Start) - Date(End).

For example , if Date(Start) = 10/10/2011 and Date (End) = 10/28/2011

then the output should be : 10/10/2011 - 10/28/2011

This is the expression I used : [Date(Start)] & ' - ' & [Date (end)].

I am not getting the right output.

Attached is the qvw. Can you please look into this?

Re: Problem in Expression

Anonymous — Mon, 18 Jun 2012 02:56:47 GMT

Hi Uday,

You can change your calculated dimension to:

=date([Date (start)]) & ' - ' & date([Date (end)])

But instead of calculating the dimension, you might want to create this one on the script side already when you load the start and end date. Use the same syntax to generate a new dimension value which will improve the performance in the chart calculation:

date([Date (start)]) & ' - ' & date([Date (end)]) as DimensionDate

Re: Problem in Expression

Mon, 18 Jun 2012 03:14:00 GMT

Hi Johannes,

date([Date (start)]) & ' - ' & date([Date (end)]) worked.

But I did not understand the explanation :

"But instead of calculating the dimension, you might want to create this one on the script side already when you load the start and end date. Use the same syntax to generate a new dimension value which will improve the performance in the chart calculation:"

Re: Problem in Expression

Anonymous — Mon, 18 Jun 2012 03:18:07 GMT

Hi Uday,

In the script you load the following table:

LOAD article_no,

WEEK_ID,

baseline_sales,

sales,

[Incremental sales],

baseline_margin,

margin,

Incremental_profit,

Quandrant,

Region,

store_format,

LSM_type,

baseline_avg_basket_value,

avg_basket_value,

baseline_no_of_baskets,

no_of_baskets,

vendor_fund,

promotion_cost,

prom_flag,

[Date (start)],

[Date (end)]

FROM

C:\PicknPay\testing\DataVisualization_data_set_20120608_testing0617.xlsx

(ooxml, embedded labels, table is test2X);

You have both your [Date (start)] and [Date (end)] fields. If you add a new field in the script,

date([Date (start)]) & ' - ' & date([Date (end)]) as DimensionDate

you will create a new field with the value formatted in the way that you want to display in your chart already.

Then in the chart dimensions, you only have to add the "DimensionDate" field as a dimension instead of using the calculated dimension. This should make it faster for QlikView to calculate your chart which should make the business users a little bit happier

Re: Problem in Expression

Mon, 18 Jun 2012 03:27:55 GMT

Thanks Johannes,

I understood this time.

So, you are saying that instead of calculating have them preloaded.

This brings me to another question. In my data set there are certain columns like sales and baseline sales and in the chart I am creating I want to show Incremental sales which is the differnece between sales and baseline sales.

So , from design point of view can you please tell me which one is better to just have the sales and baselinesales columns in the dataset, or sales, baseline and incrementalsales(calclated) or just incremental_sales column in the dataset. I am talking millions of records here.