topic Reg:Query on SET analysis with Macro in QlikView

Reg:Query on SET analysis with Macro

Mon, 05 Apr 2010 13:04:34 GMT

Hi All,

I am developing BOM report which used to find out the variance between the BOM ids interms of Cost.For that I am selecting

Area ID,Production ID And Bom ID.Then I will click button will be known as SET first. Again I will select Area ID,Production ID And Bom ID and will click second button.It will known as SET Second.

I am storing the values which is selected in ListBox in variable using Macro.So in pivot table,I am summing up the first value and second value in two diffrent Expression

The first expression is subracted with Second Exp to find out Variance.Now thing is when I click second button all the values is geeting equal so final value coming as Zero.

Note:

Here I have atatched My Macro.Pls find the attachment and do the needful

EXP1:BM1

EXP2:BM2

Varince BM1-BM2

Reg:Query on SET analysis with Macro

mike_garcia — Mon, 05 Apr 2010 19:10:50 GMT

Are you using the Expressions as you posted them at the end? or are you using them with set analysis?

If the Bookmarks were created correctly, the expression should be:

sum({BM1} Cost) - sum({BM2} Cost)

Regards,

Mike.

Reg:Query on SET analysis with Macro

Tue, 06 Apr 2010 07:13:13 GMT

Hi Mike,

I am using the same type of expression in my pivot table.

Column 1-Sum({BM1} Cost)

Column2-Sum({BM2} Cost)

Column 3-Column1-Column2

In Macro am Creating BookMark like This:

sub BM1
ActiveDocument.RemoveDocBookmark "BM1"
ActiveDocument.CreateDocBookmark false, "BM1"
ActiveDocument.RecallDocBookmark "BM1"

set val=ActiveDocument.Fields("DATAAREAID").GetSelectedValues
ActiveDocument.Variables("DATAAREAID1").SetContent val.item(0).text, true

set val=ActiveDocument.Fields("dim_ITEM_ID_PROD").GetSelectedValues
ActiveDocument.Variables("BM1_Prod_item").SetContent val.item(0).text, true

set val=ActiveDocument.Fields("dim_PROD_COST_CALC_ID").GetSelectedValues
ActiveDocument.Variables("BM1_Calc_ID").SetContent val.item(0).text, true

end sub

sub BM2
ActiveDocument.RemoveDocBookmark "BM2"
ActiveDocument.CreateDocBookmark false, "BM2"
ActiveDocument.RecallDocBookmark "BM2"

set val=ActiveDocument.Fields("DATAAREAID").GetSelectedValues
ActiveDocument.Variables("DATAAREAID2").SetContent val.item(0).text, true

set val=ActiveDocument.Fields("dim_ITEM_ID_PROD").GetSelectedValues
ActiveDocument.Variables("BM1_Prod_item").SetContent val.item(0).text, true

set val=ActiveDocument.Fields("dim_PROD_COST_CALC_ID").GetSelectedValues
ActiveDocument.Variables("BM1_Calc_ID").SetContent val.item(0).text,true

end sub

So when I click Button in front end after selecting AREA ID,PROD ID,BOM ID I want to know diffrence between these BOM ID

in terms of Cost.But now when I click second button all values become zero.So kindly help me out in this issue...