topic Re: Help required in the Calculated Dimension in QlikView

Help required in the Calculated Dimension

qlikviewforum — Fri, 24 Feb 2017 09:26:34 GMT

Against each date(MM/DD/YYYY) i have month value as (YYYYMM). Since I am not using the date in the dimension it is showing duplicate value which is same as many dates in the month. So to avoid this duplication i want to get rid of the duplicates in the dimension. Please help me to write the calculated expression for the same.

Re: Help required in the Calculated Dimension

dsharmaqv — Fri, 24 Feb 2017 09:32:17 GMT

can you please share expected O/P

Re: Help required in the Calculated Dimension

qlikviewforum — Fri, 24 Feb 2017 09:59:41 GMT

Currently the O/P is (Since we use date range in the sales expression there is duplicates)

State City City ID Month Sales

A B B1 201601 123

Expected O/P

State City City ID Month Sales

A B B1 201601 123

Re: Help required in the Calculated Dimension

qlikviewforum — Fri, 24 Feb 2017 10:41:13 GMT

Any one has any idea how this can be achieved?

Re: Help required in the Calculated Dimension

dsharmaqv — Fri, 24 Feb 2017 11:56:13 GMT

Maybe like

Table:

LOAD

Field1 & Field2 as Key,

Field1,

Field2,

Field3

FROM ....

WHERE NOT EXISTS(Key, Field1 & Field2);

DROP FIELD Key;

Only first occurence of Key value will be loaded into the resident table. Field3 is not considered for distinctness.

Re: Help required in the Calculated Dimension

qlikviewforum — Fri, 24 Feb 2017 12:05:17 GMT

Thanks for your response.

The repeatation what is see in the chart is not because of the data. It is because as i mentioned the expressions contain date range. Due to which there is a repeatation. Hope you got the issue. If not please let me know i will explain further.

Re: Help required in the Calculated Dimension

Anonymous — Fri, 24 Feb 2017 13:08:28 GMT

I think what is happening here is a dimensions problem.You need to resolve this within your load query.

1. You need to declare the YYYYMM field in your as month. e.g =Month(yyyymm) i.e =Month(month year filed name).

2. The problem ith having it as is inside of your query is that it is just a number and means nothing. You need to declare what it is in your load script first. Think of your report as a fruit salad and a tomato is the yearmonth field. You cannot just chuck it in. You would have to change it into a guava or something before you include it.

Your result is because you put the tomato in along with the apples and the rest.

Send us an example if I am wrong .

Thanks,

Re: Help required in the Calculated Dimension

ahaahaaha — Fri, 24 Feb 2017 16:39:41 GMT

Hi Rikab,

May be

[MonthValue] - dimension,

Aggr(Sum(Volume), [MonthValue]) - expression?

Regards,

Andrey

Re: Help required in the Calculated Dimension

qlikviewforum — Mon, 27 Feb 2017 08:38:03 GMT

Sorry this didn't work out.

Re: Help required in the Calculated Dimension

dsharmaqv — Mon, 27 Feb 2017 08:44:54 GMT

can you please post your app so that we can look where the issue is

Re: Help required in the Calculated Dimension

qlikviewforum — Mon, 27 Feb 2017 11:24:41 GMT

Due to security reason I may not be able to share.I will see how can I share the app.

Re: Help required in the Calculated Dimension

dsharmaqv — Mon, 27 Feb 2017 11:33:51 GMT

I can understand

I am not asking with real data .... sample app will help us in understanding your issue.