Help required in the Calculated Dimension

[qlikviewforum]

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.

[dsharmaqv]

can you please share expected O/P

[qlikviewforum]

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

A       B     B1       201601  123

A       B     B1       201601  123

A       B     B1       201601  123

A       B     B1       201601  123

.

.

.

Expected O/P

State City City ID Month   Sales

A       B     B1       201601  123

[qlikviewforum]

Any one has any idea how this can be achieved?

[dsharmaqv]

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.

[qlikviewforum]

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.

[Anonymous · Accepted solution]

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,

[ahaahaaha]

Hi Rikab,

May be

[MonthValue] - dimension,

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

Regards,

Andrey

[qlikviewforum]

Sorry this didn't work out.

[dsharmaqv]

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