Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor
Contributor

dollar expansion set analysis

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!

1 Solution

Accepted Solutions
Highlighted

Or this

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

View solution in original post

5 Replies
Highlighted
MVP & Luminary
MVP & Luminary

Try this:

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

talk is cheap, supply exceeds demand
Highlighted

Or this

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

View solution in original post

Highlighted
Contributor III
Contributor III

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

Try this
Highlighted
Contributor
Contributor

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?
Highlighted
Contributor
Contributor

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