topic Calculation dimension needed in QlikView

Calculation dimension needed

Wed, 25 Nov 2020 16:16:04 GMT

Hi all,

I had a scenario like i have sales fact table and purchases fact table and one dimension table model master,

my requirement is in one report i am showing model wise sales and purchases things in pivot table, but in data modelling i don't have a relation for purchases and sales, and also model master dimension table i created duplicate for purchases table.So for sales fact table one modelmaster table i have and purchases fact table one modelmaster table, now i want is i need a calculated dimension which is having both sales model field and purchases model field as one dimension in report.

Thanks in advance for all!

Re: Calculation dimension needed

Anonymous — Thu, 08 Dec 2016 06:35:45 GMT

Hi,

we do have option of adding calculated dimension in straight and pivot table as well. If you are looking for adding simple calculated dimension then u can go with this else u can share ur sample data so we can get more idea about ur modeling part.

Thanks,

Re: Calculation dimension needed

Gysbert_Wassenaar — Thu, 08 Dec 2016 07:47:06 GMT

You can't create a new calculated dimension by combining two unrelated dimensions. You will have to remodel your data in the script. I suggest creating a combined fact table that contains both the sales and the purchases.

Re: Calculation dimension needed

Thu, 08 Dec 2016 08:53:15 GMT

What you told is nice idea, But i have tables, but i have 4 relations fact tables for sales part and six relational fact tables for purchases part, if i do concatenating of them will get a chance of in consisting results and also circular loops.

Re: Calculation dimension needed

Gysbert_Wassenaar — Thu, 08 Dec 2016 08:56:05 GMT

No, if you concatenate all the fact tables you will have only one fact table and no chance of inconsistent results and circular loops.

Re: Calculation dimension needed

Thu, 08 Dec 2016 12:06:06 GMT

i will completely explain my data model

SALES:

BILLMASTER--> BILLDETAILS-- <modelmaster,branchmaster

--->

Purchases:

Purchases--> purchasesdetails-->...

Purchasesreturns-->purchasereturndetails- ->modelmaster,Branchmaster

if do concatenate all this fact tables i may get data consistency issues ,,,,,

Re: Calculation dimension needed

Gysbert_Wassenaar — Thu, 08 Dec 2016 12:37:32 GMT

I doubt it, but if you're sure you will then you won't be able to solve your problem. You cannot create a calculated dimension from two unrelated fields.