Get definition of a variable from QlikView

[carlos_horta1]

Hi,

I would like to know if there is a way to get the definition of a variable on QlikView.

I have a Variable called vSETYTD that is used when we want to return the Year To Day sales. If I select 2018 and January on my filters, the variable shows this:

{ $<Year={2018},MonthDayNo={"<131"},Month=,[Month Year]=,Quater=,Date=>}

So, on my straight table I have the expression:

=sum($(vSETYTD) Sales)

But now, I want to also only have the sales greater than 0. I have tried:

=sum({$<Sales={">0"}, $(vSETYTD)>} Sales)

=sum({$<Sales={">0"}>, $(vSETYTD)} Sales)

But no luck.

I would like to know how MonthDayNo is defined so I can just copy the expression instead of using the variable.

If there any way I can achieve this?

Thanks

[sunny_talwar]

Try this

Year={2018},MonthDayNo={"<131"},Month=,[Month Year]=,Quater=,Date=

and then this

=sum({ $<$(vSETYTD)>} Sales)

and this

=sum({$<Sales={">0"}, $(vSETYTD)>} Sales)

[vitaliichupryna]

Hi Carlos,

Problem with your variable vSETYTD, it have brackets:

{ $<Year={2018},MonthDayNo={"<131"},Month=,[Month Year]=,Quater=,Date=>}

You should remove brackets in variable:

Year={2018},MonthDayNo={"<131"},Month=,[Month Year]=,Quater=,Date=

Then you can use it, but with small changes in the expression:

sum({$<$(vSETYTD)>} Sales)

sum({$<Sales={">0"}, $(vSETYTD)>} Sales)

Or you can add Sales={">0"} into variable:

{ $<Year={2018},MonthDayNo={"<131"},Sales={">0"} ,Month=,[Month Year]=,Quater=,Date=>}

Thanks,

Vitalii

[carlos_horta1]

That's the thing, I can't modify the variable. The way I get to qlikview is with a link and I can just see the data and create new tabs but i can't modify any variables. So I was hoping there was a way to at least see how MonthDayNo was defined so i can write the whole expression instead of using vSETYTD.

[sunny_talwar]

Create a new straight table with just $(vSETYTD) as your expression.... make sure not to get any label. Export the chart to Excel and your label will be the definition of your variable.

[carlos_horta1]

It didn't work.

[carlos_horta1]

The label returns the result inside the brackets but not the formula.

{$<Year={2018}, MonthDayNo={"<=331"},Month=,[Month Year]=,Quarter=,Date=>}

I know that Year={$(#=Only(Year))} and that is why I get 2018. But I haven't found the formula for MonthDayNo

[vitaliichupryna]

Looks like it returns last day of month.

131 = January31, so if you want see YTD data by 14 February, you should write: 214

{ $<Year={2018},MonthDayNo={"<214"},Sales={">0"}, Month=,[Month Year]=,Quater=,Date=>}

Thanks,

Vitalii

[vitaliichupryna · Accepted solution]

So MonthDayNo can be calculated as Max(MonthDayNo)

MonthDayNo = {"<=$(=max(MonthDayNo ))"}

Thanks,

Vitalii

[carlos_horta1]

You're right, that is exactly what is doing and it makes sense because is getting all the sales up to the last day of the month so today's sales are not included.

Thank you so much Vitalii Chuprina.