topic Re: Get definition of a variable from QlikView in App Development

Get definition of a variable from QlikView

carlos_horta1 — Thu, 01 Nov 2018 15:57:32 GMT

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

Re: Get definition of a variable from QlikView

sunny_talwar — Thu, 01 Nov 2018 16:05:39 GMT

Try this

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

and then this

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

and this

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

Re: Get definition of a variable from QlikView

vitaliichupryna — Thu, 01 Nov 2018 16:08:30 GMT

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

Re: Get definition of a variable from QlikView

carlos_horta1 — Thu, 01 Nov 2018 16:22:55 GMT

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.

Re: Get definition of a variable from QlikView

sunny_talwar — Thu, 01 Nov 2018 16:24:41 GMT

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.

Re: Get definition of a variable from QlikView

carlos_horta1 — Thu, 01 Nov 2018 16:29:42 GMT

It didn't work.

Re: Get definition of a variable from QlikView

carlos_horta1 — Thu, 01 Nov 2018 16:36:11 GMT

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

Re: Get definition of a variable from QlikView

vitaliichupryna — Thu, 01 Nov 2018 16:43:52 GMT

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

Re: Get definition of a variable from QlikView

vitaliichupryna — Thu, 01 Nov 2018 16:47:27 GMT

So MonthDayNo can be calculated as Max(MonthDayNo)

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

Thanks,

Vitalii

Re: Get definition of a variable from QlikView

carlos_horta1 — Thu, 01 Nov 2018 17:10:41 GMT

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.