topic Re: dollar expansion set analysis in QlikView

dollar expansion set analysis

Anonymous — Fri, 16 Nov 2018 17:41:43 GMT

Hi,

I'm struggling with the syntax for a dollar expansion within set analysis. My code is as follows;

=Sum(
{<
    [DateType]={"Budget 2018"},
    [Measure]={"PF Units"},
    Year={2018},
    MonthNum={"<=$(=Month(=Max([Transfers.Transfer Date])))"}
    >}
    [Budget 2018]
    )

So I am trying to calculate the month of the maximum date in the field "Transfers.Transfer Date", and use the result as part of the set analysis.

Is my syntax correct?

Grateful for any help!

Re: dollar expansion set analysis

Gysbert_Wassenaar — Fri, 16 Nov 2018 17:42:53 GMT

Try this:

=Sum(
{<
[DateType]={"Budget 2018"},
[Measure]={"PF Units"},
Year={2018},
MonthNum={"<=$(=Month(Max([Transfers.Transfer Date])))"}
>}
[Budget 2018]
)

Re: dollar expansion set analysis

sunny_talwar — Fri, 16 Nov 2018 17:57:41 GMT

Or this

=Sum(
 {<
     [DateType] = {"Budget 2018"},
     [Measure] = {"PF Units"},
     Year = {2018},
     MonthNum = {"<=$(=Num(Month(Max([Transfers.Transfer Date]))))"}       
    >}
    [Budget 2018]
    )

Re: dollar expansion set analysis

Rojarao — Fri, 16 Nov 2018 19:13:39 GMT

=Sum(
{<
[DateType]={"Budget 2018"},
[Measure]={"PF Units"},
Year={2018},
MonthNum={"<=$(=Month(Max([Transfers.Transfer Date])))"}
>}
[Budget 2018]
)

Try this

Re: dollar expansion set analysis

Anonymous — Fri, 16 Nov 2018 21:23:12 GMT

Thanks for the responses so far. I've tried them out but still no luck. It seems as though the code I have around the formula is correct, i.e. the {"<=$(=Month at the start, and the "} at the end.

Interestingly I get the right result when I replace it with MonthNum={10} or even MonthNum={"10"}

I also know that the formula Month(Max([Transfers.Transfer Date]) returns the number 10 as I have tested that in a separate text box.

So I really don't understand why this isn't working.

Any other ideas please?

Re: dollar expansion set analysis

Anonymous — Fri, 16 Nov 2018 21:49:10 GMT

Wait, I was being daft, the solution from Stalwar1 above worked. Thank you all for your help!!